47

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?

5
  • 1
    Why not profile it and let the rest of us know the outcome? Generally speaking, performance very much outweighs style preference. May 22, 2011 at 1:22
  • 3
    "result in the same query plans on every DBMS I've ever tried" If this could have an answer in terms of performance, it'd have asked it on stackoverflow.com. alas, they are the same query. May 22, 2011 at 1:24
  • Ah.. Missed that :) May 22, 2011 at 1:26
  • 2
    "Subjective" does not mean "what's your opinion". I've edited this to kind of meet the criteria laid out in the FAQ.
    – Aaronaught
    May 22, 2011 at 1:31
  • I also tend toward that style to minimize surprise I think you just answered your own question. Surprises are bad.
    – Pieter B
    Apr 23, 2015 at 7:22

9 Answers 9

63

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.

2
  • This is the only sensible way to do it especially once you get past two tables, or need a combination of left, right, and full joins.
    – aglassman
    Jan 14, 2014 at 22:09
  • 6
    +1 For "separation of concerns" joins bring data together, where clauses dictate the subsets of data you are interested in.
    – user69037
    Jan 15, 2014 at 1:19
40

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.

1
  • Thank you for the explanation. When I was taught we were shown both syntax, but the difference was not explained. I sometimes managed to produce queries with missing where's which frankly would have increased the amount of writing over just explicitly joining in the first place.
    – awiebe
    Oct 6, 2018 at 12:04
8

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

1
  • 2
    You've slightly misunderstood those docs, however. Firstly, there actually are three thresholds. One fires the GEQO as you pointed out; the other two (from and join collapse limits) end up making the planer stick to picking applicable indexes rather than re-organizing the join order. Secondly and just as importantly, the queries are rewritten as they are parsed. This results in the first of the example queries getting parsed into the exact same query tree as that of the second one -- the thresholds then let PG know if it should try to re-order the joins or not. May 22, 2011 at 16:07
8

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...

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.

3

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.

1

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.

2
  • Of course that left join syntax never did provide the correct results reliably (see BOL for SQL Server 2000) so even if it was faster, I would have replaced it.
    – HLGEM
    Sep 16, 2014 at 21:30
  • I never encountered that, and searching with the asterisk never ends well, do you have an example?
    – Bill
    Sep 17, 2014 at 23:02
-1

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.

2
  • I think this answer doesn't really add anything without saying why it was deprecated and shouldn't be used. Apr 23, 2015 at 10:28
  • 1
    @RemcoGerlich why it has been deprecated isn't under discussion here. What's under discussion here is whether to use the old or new syntax. Whether one is better then the other or not is moot: you shouldn't use old syntax. The why question is another discussion. (one that has been settled 20 years ago.)
    – Pieter B
    Apr 23, 2015 at 10:59
-4

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.

1
  • Downvoters: Is there anything factually incorrect in this response, or were they just "disgree with you" downvotes? Oct 10, 2019 at 6:29

Not the answer you're looking for? Browse other questions tagged or ask your own question.