17

What is the difference between the query

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

and this one

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons, Orders
WHERE Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName
Jeyanth Kumar
  • 1,589
  • 3
  • 23
  • 48
  • 1
    possible duplicate of [INNER JOIN ON vs WHERE clause](http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause), [What's the difference between the two SQL join notations?](http://stackoverflow.com/questions/8251294/whats-the-difference-between-the-two-sql-join-notations), [Is there a difference using join and select from multi-tables?](http://stackoverflow.com/questions/2592938/is-there-a-difference-using-join-andselect-from-multi-tables), etc. – onedaywhen Feb 28 '12 at 13:36
  • Can I assume that in absence of where clause the select returns e a cross join? – Ahmed Ziyad Apr 30 '19 at 06:51

4 Answers4

18

There is a small difference in syntax, but both queries are doing a join on the P_Id fields of the respective tables.

In your second example, this is an implicit join, which you are constraining in your WHERE clause to the P_Id fields of both tables.

The join is explicit in your first example and the join clause contains the constraint instead of in an additional WHERE clause.

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

They are basically equivalent. In general, the JOIN keywords enables you to be more explicit about direction (LEFT, RIGHT) and type (INNER, OUTER, CROSS) of your join.

ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
Matthias Meid
  • 12,455
  • 7
  • 45
  • 79
3

This SO posting has a good explanation of the differences in ANSI SQL complaince, and bears similarities to the question asked here.

While (as it has been stated) both queries will produce the same result, I find that it is always a good idea to explicitly state your JOINs. It's much easier to understand, especially when there are non-JOIN-related evaluations in the WHERE clause.

Explicitly stating your JOIN also prevents you from inadvertently querying a Cartesian product. In your 2nd query above, if you (for whatever reason) forgot to include your WHERE clause, your query would run without JOIN conditions and return a result set of every row in Persons matched with every row in Orders...probably not something that you want.

Community
  • 1
  • 1
Aaron
  • 55,518
  • 11
  • 116
  • 132
2

The difference is in syntax, but not in the semantics.

The explicit JOIN syntax:

  • is considered more readable and
  • allows you to cleanly and in standard way specify whether you want INNER, LEFT/RIGHT/FULL OUTER or a CROSS join. This is in contrast to using DBMS-specific syntax, such as old Oracle's Persons.P_Id = Orders.P_Id(+) syntax for left outer join, for example.
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167