Sql connection syntax

Question

I'm kind of new to sql spelling, and I have a question about joins. Here is an example:

select bb.name from big_box bb, middle_box mb, little_box lb where lb.color = 'green' and lb.parent_box = mb and mb.parent_box = bb; 

So, tell me that I am looking for the names of all the large boxes that have enclosed somewhere in them a small box, green. If I understand correctly, the above syntax is another way to get the same results that we could get using the "join" keyword.

Questions: is the selection statement above effective for the task? If not, what is the best way to do this? Is the syntax syntax operator for the join or is it really doing something else?

If you have links to any good material on this topic, I would love to read about it, but since I do not know exactly what this technique is called, I am having problems finding it.

Answer 1

You are using implicit join syntax. This is equivalent to using the JOIN keyword, but it is recommended that you completely exclude this syntax and use explicit joins instead:

 SELECT bb.name FROM big_box bb JOIN middle_box mb ON mb.parent_box = bb.id JOIN little_box lb ON lb.parent_box = mb.id WHERE lb.color = 'green' 

You also did not have a column name in the join condition. I guessed that the column is called id .

This type of query should be effective if the tables are indexed correctly. In particular, there should be restrictions on the foreign key on the conditions of the connection and the index on little_box.color .

The problem with your query is that if there are several green boxes in the same field, you will get duplicate rows. These duplicates can be removed by adding DISTINCT after SELECT.