Difference between SQL JOIN and a two-table query

Question

What is the difference between the request

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 

Answer 1

There is a slight difference in syntax, but both queries perform a join in the P_Id fields of the corresponding tables.

In your second example, this is an implicit join that you limit in your WHERE P_Id fields of both tables.

In your first example, the connection is explicit, and the join clause contains a constraint instead of the optional WHERE .

Answer 2

They are basically equivalent. In general, the JOIN keywords allow you to more clearly define the direction ( LEFT , RIGHT ) and type ( INNER , OUTER , CROSS ) of your connection.

Answer 3

This SO post has a good explanation of the differences in ANSI SQL complaince and is similar to the question asked here.

While (as has been said) both queries will produce the same result, I believe that it is always useful to clearly indicate your JOINs. This is much easier to understand, especially when there are non-JOIN bounds in the WHERE clause.

Explicitly specifying your JOIN also prevents unintentional access to the Cartesian product. In your second case above, if you (for some reason) forgot to include a WHERE clause, your query will work without JOIN conditions and return a set of results for each row in Persons corresponding to each row in Orders ... probably not that is what you want.

Answer 4

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

Explicit JOIN syntax:

• considered more readable and
• allows you to perform a clean and standard way to indicate whether you want INNER , LEFT/RIGHT OUTER or CROSS join. This, in contrast to using a DBMS-specific syntax, for example, the old Oracle Persons.P_Id = Orders.P_Id(+) syntax for a left outer join, for example.