8

Possible Duplicate:
Explicit vs implicit SQL joins
Is there a difference using join andselect from multi-tables?
SQL Joins: Future of the SQL ANSI Standard (where vs join)?

What is the difference between JOIN and declaring multiple tables in the FROM clause?

Such as:

SELECT *
FROM  table1 AS t1,
      table2 AS t2
WHERE t1.id = t2.id

Compared to:

SELECT *
FROM table1 AS t1
INNER JOIN table2 AS t2 ON t2.id = t1.id
Darren
  • 68,902
  • 24
  • 138
  • 144
mawburn
  • 2,232
  • 4
  • 29
  • 48
  • 9
    in the first syntax easier to forget to add the = statement causing issues.. Second statement easier to read and harder to get wrong... – Osama Javed Apr 20 '12 at 14:47
  • possible duplicate of [SQL Joins: Future of the SQL ANSI Standard (where vs join)?](http://stackoverflow.com/questions/3684259/sql-joins-future-of-the-sql-ansi-standard-where-vs-join) or [MySQL: Inner join vs Where](http://stackoverflow.com/questions/5273942/mysql-inner-join-vs-where) you put SQL-Server and MySQL in your tags or the generic [Explicit vs implicit SQL joins](http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins) – Conrad Frix Apr 20 '12 at 14:51

3 Answers3

7

The second version, with the explicit JOIN and join condition is standardized SQL.

The implicit join syntax with a WHERE clause is deprecated syntax (or, rather, considered bad) - partially because it is easy to forget the WHERE clause and cause a Cartesian product.

Oded
  • 489,969
  • 99
  • 883
  • 1,009
2

Why Use the new syntax?

As others have stated, the new syntax has become the preferred convention. In larger queries the new syntax is easier to read, debug, and ensure the join criteria is added (meaning no accidental CROSS JOINS.

Is the old syntax deprecated (for inner joins)?

Not according to ANSI -- both are valid, even if the first is disfavored. Although, performing outer joins in the old syntax has been deprecated -- mainly because it can be ambiguous.

How consensus is the "use the new syntax" view ?

Community
  • 1
  • 1
EBarr
  • 11,826
  • 7
  • 63
  • 85
  • Could you define a "accidental CROSS JOIN" example to this answer? –  Feb 02 '13 at 19:06
  • I have also read this "Basically the old syntax is equivalent to a CROSS JOIN - why would you code a CROSS JOIN when you want an INNER JOIN?" here>>> http://www.sqlservercentral.com/blogs/brian_kelley/2009/09/30/the-old-inner-join-syntax-vs-the-new-inner-join-syntax/ But I still have trouble seeing how this can cause a "cross join" –  Feb 02 '13 at 19:10
  • 1
    @KDawg - For the new syntax to be syntactically correct it *must* have the `on` key word (i.e., `INNER JOIN tableA **ON**`). In the old syntax your query will compile & run even without a `where` clause, let alone correct join criteria. In simple examples here it's easy to say "how could you miss the join criteria?", but in larger complicated queries it's easy, particularly when the list of tables & the where can be separated by 11-gazillion lines of sql. – EBarr Feb 03 '13 at 04:54
1

Both will output the same and are just different variations of writing the query.

SELECT *
FROM table1 AS t1
INNER JOIN table2 AS t2 ON t2.id = t1.id

Is the preferred join method as you are explicitly stating which type of join you are using, i.e. LEFT, OUTER, INNER.

Darren
  • 68,902
  • 24
  • 138
  • 144