Difference between natural join and inner join

Solution 1:

One significant difference between INNER JOIN and NATURAL JOIN is the number of columns returned.

Consider:

TableA                           TableB
+------------+----------+        +--------------------+    
|Column1     | Column2  |        |Column1  |  Column3 |
+-----------------------+        +--------------------+
| 1          |  2       |        | 1       |   3      |
+------------+----------+        +---------+----------+

The INNER JOIN of TableA and TableB on Column1 will return

SELECT * FROM TableA AS a INNER JOIN TableB AS b USING (Column1);
SELECT * FROM TableA AS a INNER JOIN TableB AS b ON a.Column1 = b.Column1;

+------------+-----------+---------------------+    
| a.Column1  | a.Column2 | b.Column1| b.Column3|
+------------------------+---------------------+
| 1          |  2        | 1        |   3      |
+------------+-----------+----------+----------+

The NATURAL JOIN of TableA and TableB on Column1 will return:

SELECT * FROM TableA NATURAL JOIN TableB
+------------+----------+----------+    
|Column1     | Column2  | Column3  |
+-----------------------+----------+
| 1          |  2       |   3      |
+------------+----------+----------+

The repeated column is avoided.

(AFAICT from the standard grammar, you can't specify the joining columns in a natural join; the join is strictly name-based. See also Wikipedia.)

(There's a cheat in the inner join output; the a. and b. parts would not be in the column names; you'd just have column1, column2, column1, column3 as the headings.)

Solution 2:

• An inner join is one where the matching row in the joined table is required for a row from the first table to be returned
• An outer join is one where the matching row in the joined table is not required for a row from the first table to be returned
• A natural join is a join (you can have either natural left or natural right) that assumes the join criteria to be where same-named columns in both table match

I would avoid using natural joins like the plague, because natural joins are:

• not standard sql [SQL 92] and therefore not portable, not particularly readable (by most SQL coders) and possibly not supported by various tools/libraries
• not informative; you can't tell what columns are being joined on without referring to the schema
• your join conditions are invisibly vulnerable to schema changes - if there are multiple natural join columns and one such column is removed from a table, the query will still execute, but probably not correctly and this change in behaviour will be silent
• hardly worth the effort; you're only saving about 10 seconds of typing

Solution 3:

A natural join is just a shortcut to avoid typing, with a presumption that the join is simple and matches fields of the same name.

SELECT
  *
FROM
  table1
NATURAL JOIN
  table2
    -- implicitly uses `room_number` to join

Is the same as...

SELECT
  *
FROM
  table1
INNER JOIN
  table2
    ON table1.room_number = table2.room_number

What you can't do with the shortcut format, however, is more complex joins...

SELECT
  *
FROM
  table1
INNER JOIN
  table2
    ON (table1.room_number = table2.room_number)
    OR (table1.room_number IS NULL AND table2.room_number IS NULL)