By Yochai Timmer


2011-04-13 19:08:55 8 Comments

I was wondering if there is any difference in the way SQL performs on these join statements:

SELECT * FROM a,b WHERE a.ID = b.ID

SELECT * FROM a JOIN b ON a.ID = b.ID

SELECT * FROM a JOIN b USING(ID)

Is there a performance difference? Or algorithmic difference?

Or is it just syntactic sugar?

5 comments

@gbn 2011-04-13 19:13:44

There is no difference in performance.

However, the first style is ANSI-89 and will get your legs broken in some shops. Including mine. The second style is ANSI-92 and is much clearer.

Examples:

Which is the JOIN, which is the filter?

FROM T1,T2,T3....
WHERE T1.ID = T2.ID AND
     T1.foo = 'bar' AND T2.fish = 42 AND
     T1.ID = T3.ID

FROM T1 
   INNER JOIN T2 ON T1.ID = T2.ID
   INNER JOIN T3 ON T1.ID = T3.ID
WHERE
   T1.foo = 'bar' AND T2.fish = 42

If you have OUTER JOINs (=*, *=) then the 2nd style will work as advertised. The first most likely won't and is also deprecated in SQL Server 2005+

The ANSI-92 style is harder to bollix too. With the older style you can easily end up with a Cartesian product (cross join) if you miss a condition. You'll get a syntax error with ANSI-92.

Edit: Some more clarification

  • The reason for not using "join the where" (implicit) is the dodgy results with outer joins.
  • If you use explicit OUTER JOINs + implicit INNER JOINs you'll still get dodgy results + you have inconsistency in usage

It isn't just syntax: it's about having a semantically correct query

Edit, Dec 2011

SQL Server logical query processing order is FROM, ON, JOIN, WHERE...

So if you mix "implicit WHERE inner joins" and "explicit FROM outer joins" you most likely won't get expected results because the query is ambiguous...

@Yochai Timmer 2011-04-13 19:19:04

Do you know if it's documented somewhere that it's just a syntax change ?

@gbn 2011-04-13 19:22:16

ANSI-92 supersedes ANSI-89? Practically, it's about clarity and maintainability. You seem to object to the JOIN syntax...

@JonH 2011-04-13 19:27:28

@Yochai Timmer 92 > 89 last I checked.

@Yochai Timmer 2011-04-13 19:28:19

didn't argue about that :)

@gbn 2011-04-13 19:29:26

@Yochai Timmer: I'll try again... The reason for not using "join the where" (implicit) is the danger with outer joins. Then if you use explicit OUTER JOINs + implicit INNER JOINs you'll still get dodgy results + you have inconsistency in usage. It isn't syntax: it's semantics

@JonH 2011-04-13 19:29:55

@Yochai Timmer - so what other documentation are you looking [email protected] hit it right on the spot. He is giving you solid information and good programming practices.

@onedaywhen 2011-04-14 08:55:23

"Which is the JOIN, which is the filter?" -- these terms are subjective so the question is loaded: cannot answer without seeing the full query and knowing its purpose. You could say for INNER JOIN it makes not difference. If they were OUTER JOINs changing (what you imply are) the filter conditions into join conditions the query results could potentially change based on NULLs but I couldn't tell whether this is 'wrong' without knowing the designer's intention.

@gbn 2011-04-28 04:59:01

@onedaywhen: not sure what you're trying to say. Do you endorse using deprecated OUTER JOIN in the WHERE syntax? Do you understand why OUTER JOIN in the WHERE is ambiguous? Do you not like clarity and semantic correctness?

@ScottEdwards2000 2017-10-16 00:35:16

@onedaywhen don't you mean one often 'filters' for the NULL produced by the ON clause in the WHERE clause?

@onedaywhen 2017-11-02 10:16:47

@ScottEdwards2000: point taken: I shouldn't try to use colloquialisms or encourage others to do so!

@JonH 2011-04-13 19:10:33

I despise when you force a join by using WHERE. It just doesn't to me look right, a dirty hack. Proper ANSI join is to use ON:

SELECT 
    p.Product,
    o.Order
FROM 
    Product p
INNER JOIN
    Order o
ON
    o.OrderID = p.OrderID

Prefer using ON when joining and WHERE to filter results. Remember WHERE is one of the last things you will use besides grouping and order by where you want to filter your results. So you shouldn't join your tables using WHERE as it is much difficult to read.

SELECT 
    p.Product,
    o.Order
FROM 
    Product p
INNER JOIN
    Order o
ON
    o.OrderID = p.OrderID
WHERE
    o.Category = 'IT'

In the end you (the developer) might not be around in the future so readability and maintainability will help the pour soul who has to take over your code :).

When I see developers use WHERE to join their tables it's usually an indication that they don't know enough T-SQL. That is my personal opinion.

@jeroenh 2011-04-13 19:13:25

the OP did not ask your opinion on whether it is 'pretty' not to use the ANSI join syntax, just about possible differences in implementation

@JonH 2011-04-13 19:14:14

@jeroenh - I think my post clarifies exactly his question.

@gbn 2011-04-13 19:15:22

@jeroenh: it isn't about pretty: it about clarity, maintainability, and using something that has been standard for 20 years nearly. +1. Are we not here to teach and encourage best practice?

@jeroenh 2011-04-13 19:22:00

I fully agree that proper JOIN syntax is more readable and better maintainable and I withdrew my downvote after the edit. But I maintain my position: the question is about possible performance/algorithmic differences, not about syntax. So this still does not answer the question. @oJmHo can we hold a decent discussion please instead of calling each other names?

@JonH 2011-04-13 19:25:41

@jeroenh - That is fine, in no way was I saying one is better then the other performance wise. It's the proper way to do things, just as it is proper for you to shower when you wake up in the morning :). You can get away with not showering but someone will smell your odor. Just as its proper to use proper coding conventions, otherwise someone will smell the bad code from the good code.

@gbn 2011-04-13 19:34:55

ha ha. I assumed implcit joins could be classed as a code smell then...?

@Pete M 2011-04-13 19:12:56

The difference is readability and maintainability. SELECT * FROM a JOIN b ON a.ID = b.ID conveys your exact intent, all in the same place.

I won't say definitively since I haven't gotten under the hood of the last query optimizer, but I'm pretty confident you're looking at a trivial difference in performance, if any.

@scrappedcola 2011-04-13 19:15:04

This is a duplicate of this SO question: Explicit vs implicit SQL joins. Generally I think the implicit (where version) is bad form and not as clear as the explicit (on version). I also think the implicit is being depreciated but not 100% on that one. The execution plan is the same for both though.

@Jason 2011-04-13 19:14:30

Related Questions

Sponsored Content

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

25 Answered Questions

[SOLVED] What is the difference between "INNER JOIN" and "OUTER JOIN"?

10 Answered Questions

[SOLVED] Improve INSERT-per-second performance of SQLite?

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

21 Answered Questions

[SOLVED] What is the difference between call and apply?

13 Answered Questions

[SOLVED] Count(*) vs Count(1) - SQL Server

11 Answered Questions

[SOLVED] INNER JOIN ON vs WHERE clause

6 Answered Questions

[SOLVED] Difference between JOIN and INNER JOIN

13 Answered Questions

[SOLVED] How to join (merge) data frames (inner, outer, left, right)

Sponsored Content