SQL JOIN: ON vs Equals

Is there any significant difference between the following?

SELECT a.name, b.name FROM a, b WHERE a.id = b.id AND a.id = 1

AND

SELECT a.name, b.name FROM a INNER JOIN b ON a.id = b.id WHERE a.id = 1

Do SO users have a preference of one over the other?

From stackoverflow

• No difference. I find the first format more readable and use the second format only when doing other types of joins (OUTER, LEFT INNER, etc).

• There is no difference, but the readability of the second is much better when you have a big multi-join query with extra where clauses for filtering.
  Separating the join clauses and the filter clauses is a Good Thing :)

  Joel Coehoorn : There's no difference for this query, but for other queries there are some things you can doe with 'INNER JOIN' style syntax that you can't do with the a,b style syntax.

  Lars Mæhlum : Joel: Very true :)

• The former is ANSI 89 syntax, the latter is ANSI 92.

  For that specific query there is no difference. However, with the former you lose the ability to separate a filter from a join condition in complex queries, and the syntax to specify LEFT vs RIGHT vs INNER is often confusing, especially if you have to go back and forth between different db vendors. I don't like the older syntax at all.

• The second form is SQL92 compliant syntax. This should mean that it is supported by all current and future databases vendors. However , the truth is that the first form is so pervasive that it is also guaranteed to be around for longer than we care.

  Otherwise they are same in all respects in how databases treat the two.

• There is no difference to the sql query engine.

  For readability, the latter is much easier to read if you use linebreaks and indentation.

  For INNER JOINs, it does not matter if you put "filters" and "joins" in ON or WHERE clause, the query optimizer should decide what to do first anyway (it may chose to do a filter first, a join later, or vice versa

  For OUTER JOINs however, there is a difference, and sometimes youll want to put the condition in the ON clause, sometimes in the WHERE. Putting a condition in the WHERE clause for an OUTER JOIN can turn it into an INNER JOIN (because of how NULLs work)

  For example, check the readability between the two following samples:

  SELECT c.customer_no, o.order_no, a.article_no, r.price
  FROM customer c, order o, orderrow r, article a
  WHERE o.customer_id = c.customer_id
  AND r.order_id = o.order_id
  AND a.article_id = r.article_id
  AND o.orderdate >= '2003-01-01'
  AND o.orderdate < '2004-01-01'
  AND c.customer_name LIKE 'A%'
  ORDER BY r.price DESC
  

  vs

  SELECT c.customer_no, o.order_no, a.article_no, r.price
  FROM customer c 
  INNER JOIN order o
     ON  o.customer_id = c.customer_id
     AND o.orderdate >= '2003-01-01'
     AND o.orderdate < '2004-01-01'
  INNER JOIN orderrow r
     ON  r.order_id = o.order_id
  INNER JOIN article a 
     ON  a.article_id = r.article_id
  WHERE  c.customer_name LIKE 'A%'
  ORDER BY r.price DESC
  

  Joel Coehoorn : Putting a condition in a where clause vs a join clause most certainly does matter for outer joins.

  Cybis : Why was this downvoted? I'm voting it back up. Brimstedt was simply showing that the second version is easier to read (and I agree with him). Joins and filters are, conceptually, two different things.

  Brimstedt : ah, yes for outer joins it makes a difference.. thanks for pointing it out, Ill edit my answer for completeness

  Brimstedt : Not that i care much about the downvote, but to the query engine, it does not matter. To humans it does, and I find it more readable. :-)

• Whilst you can perform most tasks using both and in your case there is no difference whatsoever, I will always use the second at all times.

  1. It's the current supported standard
  2. It keeps joins in the FROM clause and filters in the WHERE clause
  3. It makes more complex LEFT, RIGHT, FULL OUTER joins much easier
  4. MSSQL Help is all based around that syntax therefore much easier to get help on your problem queries

• While there is no difference technically, you need to be extra careful about doing joins using the first method. If you get it wrong by accident, you could end up doing a cartesian join between your a and b tables (a very long, memory & cpu intensive query - it will match each single row in a with all rows in b. Bad if a and b are large tables to begin with). Using an explicit INNER JOIN is both safer and easier to read.