MySQL NATURAL LEFT OUTER JOIN vs LEFT OUTER JOIN

Question

If I have the following situations:

**table 1
person| money
    A | 2
    B | 10
-------------


**table 2 
person| cars
    A | 3
    C | 10

---------------

What is the difference between the two?

SELECT * FROM table1 NATURAL LEFT OUTER JOIN table2;

produces:

person| money |cars
    A | 2     |3
    B | 10    |NULL
---------------

vs

SELECT * FROM table1 LEFT OUTER JOIN table 2 ON person

Answer 1

I think you have typos in your non-NATURAL version and what you are comparing is:

SELECT * FROM table1 NATURAL LEFT OUTER JOIN table2;

SELECT * FROM table1 LEFT OUTER JOIN table2 USING ( person );

First, I would expect the result of both to be equal.**

Second, I don't see any point in the non-NATURAL/USING version in your case because you only have the one column in common and they are consistently named.

** I was going to qualify with "relationally speaking" (e.g. column order and row order not being relevant) but OUTER JOIN is expressly designed to generate nulls, which have no place in the relational model! Therefore, I recommend you avoid OUTER JOIN entirely and take another approach e.g. it seems entirely reasonable to use 0 (cars) as a default value for persons who have no entry in table2:

SELECT person, money, cars 
  FROM table1 
       NATURAL JOIN table2
UNION
SELECT person, money, 0 AS cars 
  FROM table1 
 WHERE person NOT IN ( SELECT person FROM table2 );