5

The result set of the following 2 SQL scripts look the same. But there should be some difference. So what's it?

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons, Orders
WHERE Persons.Id_P = Orders.Id_P 


SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P

Any difference in performance?

Update

I just compared the actual query plan on SQL Server 2008 R2. They are identical. So no performance difference. Inner join is used in both scenarios.

smwikipedia
  • 61,609
  • 92
  • 309
  • 482
  • 2
    What about **readability** and **stability** of the query? Option #2 is much cleaner and you tend to forget the JOIN condition (`ON ....`) much less often than with case #1. Also: case #2 is the official **ANSI Standard** for the JOIN syntax – marc_s Mar 11 '12 at 15:19
  • @marc_s, your are right, that's another point. – smwikipedia Mar 11 '12 at 15:25
  • possible duplicate of [SQL JOIN: is there a difference between USING, ON or WHERE?](http://stackoverflow.com/questions/5654278/sql-join-is-there-a-difference-between-using-on-or-where) or [Explicit vs implicit SQL joins](http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins), but I prefer the answer to the first... – Ben Mar 11 '12 at 16:09

2 Answers2

7

Your first query:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons, Orders
WHERE Persons.Id_P = Orders.Id_P

Is indeed a JOIN. The comma (,) is shorthand notation for a JOIN.

  • 2
    That's the old school INNER JOIN. It *should* compile the same. Basically you're making a CROSS JOIN and limiting the values where they are equal (AKA INNER JOIN) – SQLMason Mar 11 '12 at 15:10
  • @smwikipedia It's actually an ANSI standard, I believe. –  Mar 11 '12 at 15:11
  • @smwikipedia None. The execution plans should be identical. –  Mar 11 '12 at 15:15
  • They're not exactly the same. The first is a full join, which you're making an inner join with the WHERE clause. Most databases these days will adjust their query plans so that they are exactly the same. – vol7ron Mar 11 '12 at 15:18
2

The statements are completely equivalent.

If you would run just this section:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons, Orders

you would get any possible combination of persons and orders (a cartesian join). With the added WHERE clause, you limit it to those combinations that matches. That's exactly what INNER JOIN does. This form is more powerful than using the JOIN keyword, as you can choose yourself exactly how what sets of rows you want to match.

For example, I recently used a cartesian join to create a list of all days occuring between two dates. That would not be possible with the JOIN keyword.

Anders Abel
  • 67,989
  • 17
  • 150
  • 217
  • Thanks for your reply. So these 2 statements are equivalent **physically** or **just logically**? – smwikipedia Mar 11 '12 at 15:12
  • 1
    That probably varies across different DB engines. A good optimizer will find out that they are equivalent, but a simple engine may have easier to understand `INNER JOIN` and perform better. You would have to test on your db engine, to see if the execution plans are the same. – Anders Abel Mar 11 '12 at 15:14
  • "I recently used a cartesian join to create a list of all days occuring between two dates. That would not be possible with the `JOIN` keyword" -- I think you are mistaken! – onedaywhen Mar 12 '12 at 10:13