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

The natural join will infer the join columns and remove the duplicate join column from the result set. — Tim Biegeleisen, Nov 02 '16 at 01:30
Possible duplicate of [Difference between natural join and inner join](http://stackoverflow.com/questions/8696383/difference-between-natural-join-and-inner-join) — Tim Biegeleisen, Nov 02 '16 at 01:30
I don't think your non-`NATURAL` version is valid syntax. Did you mean to use `USING` rather then `ON`? e.g. `SELECT * FROM table1 LEFT OUTER JOIN table2 USING ( person )` ? Also the space in `table 2` appears to be a typo. — onedaywhen, Nov 03 '16 at 11:25
@TimBiegeleisen: I don't agree it is a duplicate: e.g. you can't answer that other question without using range variables (which is kinda the point of natural join, BTW), whereas the OP hasn't used range variables in this question. — onedaywhen, Nov 03 '16 at 11:50

score 0 · Accepted Answer · answered Nov 03 '16 at 11:46

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 );

MySQL NATURAL LEFT OUTER JOIN vs LEFT OUTER JOIN

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

1 Answers1