SQL – Using JOIN Keyword or Not

coding-stylesql

The following SQL queries are the same:

SELECT column1, column2
FROM table1, table2
WHERE table1.id = table2.id;

SELECT column1, column2
FROM table1 JOIN table2 
ON table1.id = table2.id;

And certainly result in the same query plans on every DBMS I've ever tried.

But every so often, I read or hear an opinion that one is definitely better
than the other. Naturally, these claims are never substantiated with an
explanation.

Where I work, the second version seems to be favored by the majority of other
devs, and so I also tend toward that style to minimize surprise. But In my
heart, I'm really thinking the first one (since that's how I originally learned
it).

Is one of these forms objectively better than the other? If not, what would be the reasons to use one over the other?

Best Answer

I find that the second form is better. That may be because that is how I learned it, I'll admit, but I do have one concrete reason - separation of concerns. Putting the fields you are using to join the tables in the where clause can lead to difficulties in understand queries.

For example, take the following query:

select *
from table1, table2, table3, table4
where table1.id = table2.id
and table2.id = table3.id
and table3.id = table4.id
and table1.column1 = 'Value 1'

The above query has table joining conditions and actual business logic conditions all combined into a single space. With a large query, this can be very difficult to understand.

However, now take this code:

select *
from table1 join table2 on table1.id = table2.id
join table3 on table2.id = table3.id
join table4 on table3.id = table4.id
where table1.column1 = 'Value 1'

In this case, anything having to do with the tables or how they relate is all isolated to the from clause, while the actual business logic for query restriction is in the where clause. I think that is just much more understandable, particularly for larger queries.

Related Topic