0

I have been having this doubt for a while now, after some practices in SQL I started to ask myself: 'When is the right time to use NATURAL JOIN'?

Due to the enormous size of the database example that I'm using to practice my SQL skills I'm just going to put two sample queries here. Let's say I want to

Find, for each item, the total quantity sold by the departments on the second floor

The sample answer of this question is:

SELECT Item.ItemName, SUM(SaleQTY)
  FROM Item INNER JOIN Sale INNER JOIN Department
  ON Item.ItemID = Sale.ItemID
  AND Department.DepartmentID = Sale.DepartmentID
  WHERE DepartmentFloor = 2
  GROUP BY Item.ItemName
  ORDER BY Item.ItemName;

However when doing this question myself I only used NATURAL JOIN and here is my attempt:

SELECT Item.ItemName, SUM(SaleQTY)
  FROM Item NATURAL JOIN SALE NATURAL JOIN Department
  WHERE DepartmentFloor = 2
  GROUP BY Item.ItemName
  ORDER BY Item.ItemName

And it produced the exact same output as the sample answer:

ItemName           SUM(SaleQTY)
Boots - snakeproof      2
Camel saddle            1
Elephant polo stick     1
Hat - polar explorer    3
Pith helmet             1
Pocket knife - Nile     2
Sextant                 2`

I understand that the reason for an INNER JOIN is to ensure the integrity of the data by these conditions applied in the code and eliminate any data that does not satisfy them. But still I'm wondering, is NATURAL JOIN sufficient enough to crack this problem?

If not, what are some important rules to follow?

philipxy
  • 14,867
  • 6
  • 39
  • 83
2Xchampion
  • 666
  • 2
  • 10
  • 24
  • 1
    I am closing this as opinion-based. My opinion is: *Never* use `NATURAL JOIN`. It is a bug waiting to happen. You want your code to be explicit about the join keys being used, so it is readable, maintainable, re-usable, and less prone to error. `NATURAL JOIN` does not even take foreign key relationships into account. – Gordon Linoff Dec 11 '16 at 12:34
  • 2
    My opinion is: *always* use `NATURAL JOIN`. Other join types produce duplicate column names (note you have redundantly used the range variable `Item.` in your second query), which is contrary to the relational model on which SQL is (loosely) based. P.S. no join type takes foreign keys into account, and nor should they. – onedaywhen Dec 12 '16 at 08:51
  • Possible duplicate of [Inner Join vs Natural Join vs USING clause: are there any advantages?](http://stackoverflow.com/questions/35032696/inner-join-vs-natural-join-vs-using-clause-are-there-any-advantages) – philipxy Dec 18 '16 at 05:22
  • See my answer at the allegedly duplicate question. PS NATURAL JOIN is equivalent to an INNER JOIN on common columns that SELECTS only one of each. – philipxy Dec 18 '16 at 05:28

0 Answers0