[sql] INNER JOIN vs INNER JOIN (SELECT . FROM)

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?

This question is related to sql sql-server join inner-join

The answer is


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


Seems to be identical just in case that SQL server will not try to read data which is not required for the query, the optimizer is clever enough

It can have sense when join on complex query (i.e which have joings, groupings etc itself) then, yes, it is better to specify required fields.

But there is one more point. If the query is simple there is no difference but EVERY extra action even which is supposed to improve performance makes optimizer works harder and optimizer can fail to get the best plan in time and will run not optimal query. So extras select can be a such action which can even decrease performance


You did the right thing by checking from query plans. But I have 100% confidence in version 2. It is faster when the number off records are on the very high side.

My database has around 1,000,000 records and this is exactly the scenario where the query plan shows the difference between both the queries. Further, instead of using a where clause, if you use it in the join itself, it makes the query faster :
SELECT p.Name, s.OrderQty
FROM Product p
INNER JOIN (SELECT ProductID, OrderQty FROM SalesOrderDetail) s on p.ProductID = s.ProductID WHERE p.isactive = 1

The better version of this query is :

SELECT p.Name, s.OrderQty
FROM Product p
INNER JOIN (SELECT ProductID, OrderQty FROM SalesOrderDetail) s on p.ProductID = s.ProductID AND p.isactive = 1

(Assuming isactive is a field in product table which represents the active/inactive products).


There won't be much difference. Howver version 2 is easier when you have some calculations, aggregations, etc that should be joined outside of it

--Version 2 
SELECT p.Name, s.OrderQty 
FROM Product p 
INNER JOIN 
(SELECT ProductID, SUM(OrderQty) as OrderQty FROM SalesOrderDetail GROUP BY ProductID
HAVING SUM(OrderQty) >1000) s 
on p.ProductID = s.ProdctId 

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to join

Pandas Merging 101 pandas: merge (join) two data frames on multiple columns How to use the COLLATE in a JOIN in SQL Server? How to join multiple collections with $lookup in mongodb How to join on multiple columns in Pyspark? Pandas join issue: columns overlap but no suffix specified MySQL select rows where left join is null How to return rows from left table not found in right table? Why do multiple-table joins produce duplicate rows? pandas three-way joining multiple dataframes on columns

Examples related to inner-join

Trying to use INNER JOIN and GROUP BY SQL with SUM Function, Not Working Multiple INNER JOIN SQL ACCESS How to select all rows which have same value in some column Eliminating duplicate values based on only one column of the table How can I delete using INNER JOIN with SQL Server? How to use mysql JOIN without ON condition? Inner join with 3 tables in mysql SQL Inner join more than two tables MySQL INNER JOIN select only one row from second table Insert using LEFT JOIN and INNER JOIN