24
What is the difference between joining tables by JOIN or by WHERE?
Examples:
SELECT * FROM clientes c JOIN enderecos e ON c.id = e.id_cliente;
SELECT * FROM clientes c, enderecos e WHERE c.id = e.id_cliente;
That’s just a matter of better organization of the code or if it differs from the performance also of the consultation?
17
No difference, algebraically the queries are identical and will have the same performance.
Your query below is written in the pattern ANSI 89
SELECT * FROM clientes c, enderecos e WHERE c.id = e.id_cliente;
The same query written in the pattern ANSI 92
SELECT * FROM clientes c JOIN enderecos e ON c.id = e.id_cliente;
Personally I prefer to use the pattern ANSI 92, for a few reasons:
WHERE, because it is not known at first if conditions in the clase WHERE are filters or junctions.WHERE the result will be the Cartesian product among the customer tables,WHERE which has an effect of INNER JOIN and then needs to be changed to OUTER can be much more complicated6
They do exactly the same thing, they are equal both in performance and the result obtained, they only differ in syntax.
If you want to validate what I wrote above, use the EXPLAIN command:
EXPLAIN SELECT * FROM clientes c JOIN enderecos e ON c.id = e.id_cliente;
EXPLAIN SELECT * FROM clientes c, enderecos e WHERE c.id = e.id_cliente;
Note that the indexing mechanism used to obtain the results are the same.
The best way to show the difference.
6
In relation to performance, both equalize. However, note:
SELECT * FROM clientes c, enderecos e
We forgot the clause WHERE linking the keys to the tables. The result is a Cartesian product. Use JOIN prevents such cases from occurring.
The right thing would be cartesian product.
Valid, corrected :-)
By the way, I gave updote because it was the only answer that spoke of the problem in forgetting about Where.
Yeah, I don’t get this whole unexplained downvotes thing. I don’t see anything wrong with my answer.
1
There is no difference except by the code itself. In relation to performance also has no difference.
Browser other questions tagged mysql database select-sql
You are not signed in. Login or sign up in order to post.
2 Considerations: First, don’t use only Join, use Ner Join, it is more visible. Second, always use the shape with Inner Join, because in addition to being more legible, you may need to put a left Join later and it’s easier and faster to understand. About your question there is no difference.
– Tiedt Tech
related
– rray
Except for the @Rlon.Tiedt comment, no response was satisfactory regarding the "best organization" part. A multi-table query using only WHERE is almost impossible to read.
– utluiz
Whereas I prefer to use only
JOINinstead ofINNER JOIN. That’s because I organize my code with indentation to the right of the first word of each line.– Tiago César Oliveira
@Marlon.Tiedt I didn’t understand what you said about
left joins– Gabe
@Gabe, imagine an SQL with 10 tables. Everything works 100%. Then they put a new table,
CLIENTE_TITULOS. If you do Inner Join, with the client table and there are no titles, the client will not appear. Now imagine you have an SQL in the patternclientes c, enderecos e left join CLIENTE_TITULOS. looks like it got out of pattern. Looks more beautifulclientes c inner join enderecos e left join CLIENTE_TITULOS. Of course in the SQL above I omitted the fields for lack of space. Tendeu?– Tiedt Tech
@Ah, got it. You guys say use always
inner joinsinstead of "multiple"wheresmakes it more "simple" (cute :D) to insert aright|left jointhen if you need to. It makes sense.– Gabe
This even...plus more elegant, it is easier to maintain
– Tiedt Tech