CODEWITHSUNDEEP
sql
smwikipedia
smwikipedia

Reputation: 64175

Difference between a select with and without join

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.

Upvotes: 5

Views: 1111

Answers (2)

Anders Abel
Anders Abel

Reputation: 69260

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.

Upvotes: 2

user596075
user596075

Reputation:

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.

Upvotes: 7

Related Questions