INNER JOIN vs INNER JOIN (SELECT . FROM)

sql sql-server join inner-join
Karun picture Karun · Jul 20, 2012 · Viewed 228k times · Source

Is there any difference in terms of performance between these two versions of the same query?

--Version 1
SELECT p.Name, s.OrderQty
FROM Product p
INNER JOIN SalesOrderDetail s on p.ProductID = s.ProductID

--Version 2
SELECT p.Name, s.OrderQty
FROM Product p
INNER JOIN (SELECT ProductID, OrderQty FROM SalesOrderDetail) s on p.ProductID = s.ProductID

I've heard it said (by a DBA) that Version 2 is faster because it fetches, within the inner SELECT statement, only the columns that are required for the query. But that doesn't seem to make sense, since query performance (as I know) is based on number of rows affected and final list of columns returned.

The query plans for both are identical, so I'm guessing there isn't any difference between the two.

Am I correct?

Answer

Christian Hayter picture Christian Hayter · Jul 20, 2012

You are correct. You did exactly the right thing, checking the query plan rather than trying to second-guess the optimiser. :-)

Related questions

Difference between JOIN and INNER JOIN

Both these joins will give me the same results: SELECT * FROM table JOIN otherTable ON table.ID = otherTable.FK vs SELECT * FROM table INNER JOIN otherTable ON table.ID = otherTable.FK Is there any difference between the statements in performance …

Read More
How to have SQL INNER JOIN accept null results

I have the following query: SELECT TOP 25 CLIENT_ID_MD5, COUNT(CLIENT_ID_MD5) TOTAL FROM dbo.amazonlogs GROUP BY CLIENT_ID_MD5 ORDER BY COUNT(*) DESC; Which returns: 283fe255cbc25c804eb0c05f84ee5d52 864458 879100cf8aa8b993…

Read More
Conditional Inner Join

I want to be able to inner join two tables based on the result of an expression. What I've been trying so far: INNER JOIN CASE WHEN RegT.Type = 1 THEN TimeRegistration ELSE DrivingRegistration AS RReg ON RReg.RegistreringsId = R.Id …

Read More