When and when is NOT NATURAL JOIN?

I had this doubt for a while, after some practice in SQL, I started asking myself: "When is the right time to use NATURAL JOIN "?

Due to the sheer size of the sample database that I use to practice my SQL skills, I'm just going to post two sample queries here. Let's say i want

Find the total amount sold by departments on the second floor for each item.

Sample answer to this question:

 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 executing this question, I used only 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 he produced the same result 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 INNER JOIN is to ensure the integrity of the data by these conditions applied in the code, and to eliminate any data that does not satisfy them. But still I wonder is NATURAL JOIN enough to crack this problem?

If not, what are some important rules?

0
source share

Source: https://habr.com/ru/post/1241579/


All Articles