CODEWITHSUNDEEP
mysqlsqljoin
Jey
Jey

Reputation: 1511

Difference between SQL JOIN and querying from two tables

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

Upvotes: 17

Views: 6035

Answers (4)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

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.

Upvotes: 2

Aaron
Aaron

Reputation: 57748

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.

Upvotes: 3

Matthias Meid
Matthias Meid

Reputation: 12513

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.

Upvotes: 3

Oded
Oded

Reputation: 499002

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.

Upvotes: 18

Related Questions