The difference between choosing with and without a connection

Question

The resulting set of two SQL scripts looks the same. But there must be some difference. So what is 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 performance difference ?

Update

I just compared the actual query plan for SQL Server 2008 R2. They are identical. So there is a difference in performance. The inner join is used in both scenarios.

Answer 1

Your first request:

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

Really JOIN . The comma ( , ) is an abbreviation for a JOIN .

Answer 2

The operators are completely equivalent.

If you run only this section:

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

You will receive any possible combination of persons and orders (Cartesian connection). With the WHERE added, you limit it to the appropriate combinations. This is exactly what INNER JOIN does. This form is more efficient than using the JOIN keyword, as you can choose which string combinations you want to match.

For example, I recently used a Cartesian join to create a list of all days between two dates. This is not possible with the JOIN keyword.