Natural joins are evil

Motto:
If you like time bombs use them ;)

During my teaching courses I once again stumble upon ANSI SQL NATURAL JOIN syntax. And once again I suspect that creators of them had in mind something like how to write unmaintainable code. Or probably they liked time bombs ;)
OK for those unfamiliar with Natural joins let's firstly see what they mean:
"A natural join is based on all columns in the two tables that have the same name. It selects rows from the two tables that have equal values in the relevant columns."
So it joins two tables if column names are the same. There isn't any explicit join condition. And here is the biggest problem. The work has been done behind the scenes, and that is one of the biggest advantages of writing unmaintainable code.
So let's assume we have following tables:
CREATE TABLE persons (
person_id NUMBER(10) NOT NULL,
address_id NUMBER(10) NOT NULL,
surname VARCHAR2(40) NOT NULL,
first_name VARCHAR2(40) NOT NULL,
remarks VARCHAR2(1000),
CONSTRAINT person_pk PRIMARY KEY (person_id));

CREATE TABLE addresses (
address_id NUMBER(10) NOT NULL,
country VARCHAR2(40) NOT NULL,
city VARCHAR2(40),
text VARCHAR2(100) NOT NULL,
CONSTRAINT address_pk PRIMARY KEY (address_id));

ALTER TABLE persons ADD CONSTRAINT person_address_fk
FOREIGN KEY (address_id) REFERENCES addresses (address_id);

Lets add a few rows:

INSERT INTO addresses
VALUES (1, 'LATVIA', 'RIGA', 'BRIVIBAS STREET 100');

INSERT INTO persons
VALUES (1, 1, 'PLIVNA', 'GINTS', 'Actually he lives in another place');

So what would be the SQL query to get persons and their countries using NATURAL JOIN syntax?

SQL> SELECT surname, first_name, country
2 FROM persons
3 NATURAL JOIN addresses;
SURNAME
----------------------------------------
FIRST_NAME
----------------------------------------
COUNTRY
----------------------------------------
PLIVNA
GINTS
LATVIA

1 row selected.

SQL>

Nice one - we got one row as expected.
So assume times goes by and as per our new requirements we have to add some description field also in addresses table. To be consistent and following practices that differ from how to write unmaintainable code we use the same column name and data type as in persons.

ALTER TABLE addresses ADD remarks VARCHAR2(1000);

So what our query is showing now?

SQL> SELECT surname, first_name, country
2 FROM persons
3 NATURAL JOIN addresses;

no rows selected

SQL>

WoW! Where the row has gone? We didn't change our data!
Yeahhhh, everything works as expected. We added column remarks with the same name as in persons table and now join is being done using both columns. And of course data in persons.remarks column doesn't match with addresses.remarks. So our time bomb has blown up and we silently introduced a nasty bug in our system. Code is as valid as before from the compilation viewpoint, so we won't get compilation errors. It simply gives wrong result. Invalid SQL would be much better, then we'd spotted bug much easier, but this time we got exactly very nasty and in the beginning absolutely unclear bug.

There are also other things to consider:
1) natural joins between two tables without at least one common column results in cartesian product.
CREATE TABLE numbers AS
SELECT rownum id
FROM all_source;

Lets see how many rows table numbers have and how many rows we got for natural join between them:
SQL> SELECT count(*) FROM numbers;
COUNT(*)
----------
549290

1 row selected.

SQL> SELECT count(*) FROM (
2 SELECT * FROM persons
3 NATURAL JOIN numbers);
COUNT(*)
----------
549290

1 row selected.

SQL>

2) with different data types but the same column names there might be type conversion problems and quite strange error messages (see that it is NATURAL OUTER LEFT or RIGHT JOIN):

ALTER TABLE addresses MODIFY remarks NUMBER (15);

SQL> SELECT surname, first_name, country
2 FROM persons
3 NATURAL LEFT JOIN addresses;
SURNAME
----------------------------------------
FIRST_NAME
----------------------------------------
COUNTRY
----------------------------------------
PLIVNA
GINTS

1 row selected.

SQL> ed
Wrote file afiedt.buf
1 SELECT surname, first_name, country
2 FROM persons
3* NATURAL RIGHT JOIN addresses
SQL> /
SELECT surname, first_name, country
*
ERROR at line 1:
ORA-01722: invalid number

SQL>

So what to do?
1) Do not use this syntax. This is the most efficient and most obvious solution.
2) Use the same schema object naming conventions as me :) Natural joins simply never work using my favourite naming conventions.

Similar threads:
1) Natural Join and Index Monitoring in asktom.
2) Thread should one use ANSI join syntax when writing an Oracle application from Oracle-l list.