Using JOIN keyword or not

Question

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?

Answer 1

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.

Answer 2

The join syntax replaced the old comma syntax in 1992. There is currently no reason to ever write code with the comma syntax. You gain nothing and you are subject to some problems you simply don't have with explicit syntax.

In the first place as you get more complicated queries is very easy to do an accidental cross join by missing a where condition. This is something the explicit join syntax can prevent from happening as you will get a syntax error.

If you intend a cross join, the explicit join syntax will make that clear while in the implicit syntax someone doing maintenance may assume you forgot to add the where clause.

Then there is the problem of left and right joins which are problematic in at least some dbs using the implicit syntax. They are deprecated in SQL Server and in fact do not return correct results realiably even in the older versions. No query that needs an outer join should contain the implicit syntax in SQL Server.

Further, I have seen questions here and on other sites where wrong results happened when people mix the implicit and explicit joins (when adding a left join for instance), so it is a poor idea to mix them.

Finally many people who use implicit joins don't actually understand joins. This is a critical understanding you must have to effectively query a database.

Answer 3

Ha. I just happened to find a possible answer to my own question, while looking at the documentation for PostgreSQL. To summarise what this page explains, the resulting query is still the same, but the number of plans the optimizer must consider grows exponentially with the number of joins.

After about six such joins, the number is so great that the time to plan the query may be noticeable, and after around ten, the optimizer will switch from an exhaustive search of plans to a probabilistic search, and may not arrive on the optimal plan.

By setting a run-time parameter, you can instruct the planner to treat explicitly mentioned inner and cross joins differently from implicit joins, forcing them to the top of the plan, and not exploring other options.

Of note, the default behaviour is the same in either case, and that getting alternative plans requires knowledge of the dbms' internals and the peculiarities of the tables in question to get a different result

Answer 4

Well here is the set theory view of it:

When you use a comma to separate two (or more) table names what you are intending is the cartesian product. Every row of the 'left' table will be 'matched' (concatenated) with that of the right table.

Now if you write something in the where clause, it's like putting a condition on this 'concatenation' telling which rows to 'concatenate' with which rows.

This is actually "joining" the rows :) and hence the join keyword that helps provide a more readable syntax and is more understandable that you 'indeed' want to join on some common values. Similar to what @Dustin has clarified above.

Now, every DBMS is smart i.e., it doesn't calculate the cartesian product first and then filter out the data (extremely wasteful) but rather does so based on the query structure. The only thing that I can think of is, when you ask it to 'join' it's like making the joining activity explicit and probably helps run the code faster (by how much? You'll have to profile it and see) but in the comma separated case, it needs some time to 'figure' out the optimum strategy. I may be wrong, but I'm just making an educated guess as to how one would code it...

Answer 5

I think it's generally better to use JOIN statements for that case.

If, in the future, a situation arises that requires changing the statement from an INNER JOIN to an OUTER JOIN, this will be much easier to do with the second statement.

Answer 6

Any RDBMS is going to make them be the same thing in terms of execution. It comes down to whether one is more readable and expressive.

Use the JOIN so it's clear what is join matching and what is actual selection, as in:

select name, deptname
from people p, departments d
where p.deptid = d.id and p.is_temp = 'Y'

vs.

select name, deptname
from people p
    inner join departments d on p.deptid = d.id
where p.is_temp = 'Y'

The latter case makes it immediately clear which is the join condition, and which is the selection criterion.

Answer 7

I have only ever once seen the two result in a different set of optimizations and if memory serves it was in ms-sql2k on a really hairy query. In that one example the old form used with *= resulted in about 4x faster performance. Nobody, including our Microsoft tech guys could ever explain why. The MS guys labeled it a mistake. I have never seen it again.

Since most RDBMS are smart enough not to do the full cartesians, the biggest reason I can think of not to use it (besides that it is depreciated) is that most of the people under 30-35 that I have worked with have never seen the old form before and get terribly lost when they encounter it.

Answer 8

The old style has been deprecated, you shouldn't use it.

There shouldn't even be an argument over which one is better or not. New code shouldn't use the old syntax.

Answer 9

One reason for the more terse syntax is that it's more terse, so if you're comfortable with it it's easier to read. I think of the verbose case as similar to writing out arithmetic in COBOL, e.g. MULTIPLY A BY B GIVING C.