2

I have two SQL queries one with a WHERE and one with JOIN.

SELECT * FROM Table1 T1, Table2 T2 WHERE T1.Key = T2.Key AND T2.Key = T1.Key

SELECT * FROM Table1 T1 JOIN Table2 T2 ON T1.Key = T2.Key And T2.Key = T1.Key

Are there any differences in the two queries? If they are the same, which one is more efficient to use?

Wesgur
  • 3,140
  • 3
  • 18
  • 28
  • 2
    The second isn't syntactically valid ;) (table aliases missing). you only need to specify the condition once. – collapsar Feb 13 '18 at 19:14
  • 3
    `JOIN` has been around for over 20 years. Use it. – Eric Feb 13 '18 at 19:19
  • If I saw the first in a code review, a lot of time would be wasted explaining what proper `JOIN` syntax is and why it is preferable. Don't do that to me! – Gordon Linoff Feb 13 '18 at 19:41

2 Answers2

1

Your first query uses ANSI-89 SQL syntax, your second query the more modern ANSI-92 join syntax. Functionally they are equivalent. The second syntax is easier to read because it keeps the condition near the joined table. That's far more visible with multiple joins.

See this question for more details.

Andomar
  • 232,371
  • 49
  • 380
  • 404
1

Yes, both queries will give the same results. The second one uses explicit join and is the recommended one.

As for efficiency. Most database will optimize both queries to same execution plan, but very few databases may optimize the second one better.

Racil Hilan
  • 24,690
  • 13
  • 50
  • 55