-1

I am practicing SQL joins on learnsql.com and when joining three tables, I'm having trouble determining which table should be to the left of the join clause and which should be to the right.

Show the name of each product and its calorific value for all products that in the ‘dairy’ department.

Since the question begins by asking for the name of each product, I made the assumption that my answer should be ... FROM product p .... and then I would left join the nutrition_data and department data on it.

List all products that have fewer than 150 calories. For each product show its name (rename the column to product) and the department in which can be found (name the column department).

I interpreted the second question the same way, that it should be a list of all the products (... FROM product ...) and then the department and nutrition_data would be joined to that.

Below is a picture of the tables and the answer to the first question, the wrong answer to the second question and the right answer to the second question.

https://i.stack.imgur.com/OBG7K.jpg

Because the question asked for a list of all the products, I thought the product table should be the first table after the FROM clause and then all the other tables would be joined to it. This logic worked for the first question, and since the second question is similar in nature, I assumed it was the same logic. However for the second, the solution is ... FROM department ... and then the product and nutrition_data is joined to the department table.

How do I determine which table should be to the left of my join?

philipxy
  • 14,867
  • 6
  • 39
  • 83
giraffe_beard
  • 19
  • 2
  • 2
    You don't need left joins at all unless you think there are unmatched records that you need from table on the left side. For instance, if you had products without departments or without calorific value. Otherwise, inner joins are best. Anyway, as a rule when you are asking questions at SO - provide your data and your queries as text, and try to create a reproducible example. – topsail Dec 06 '22 at 22:40
  • [How to select from two tables in MySQL even if not all rows in one table have corespondents in the other?](https://stackoverflow.com/q/3969452/3404097) [What is the difference between "INNER JOIN" and "OUTER JOIN"?](https://stackoverflow.com/a/46091641/3404097) [Re SQL querying.](https://stackoverflow.com/a/33952141/3404097) – philipxy Dec 06 '22 at 23:51
  • Please ask 1 specific researched non-duplicate question. Debug questions require a [mre]. [Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097) Please use (block or inline) quote format for quotes & give credit. [ask] [Help] [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) – philipxy Dec 06 '22 at 23:54

1 Answers1

0

"all products" is what calls an outer join: all products even when they don't have calorific information

"all products that have fewer than 150 calories" cannot show the products without calorific information. Then I see no outer join here. Except if no calorific information means no calories

FranckPachot
  • 414
  • 4
  • 10