Correlated subqueries do not have to execute once per row - on the contrary, they are equivalent to outer joins, and they may have the same execution plans and the same real execution costs (if we retrieve only one column via a correlated subquery, of course)
Let me provide a script which compares the performance of a query with one correlated subquery vs. an equivalent out join. First, we need two tables for our example:
CREATE TABLE dbo.Parent
(
ID INT NOT NULL
PRIMARY KEY ,
ParentNumber INT NOT NULL
) ;
GO
CREATE TABLE dbo.Child
(
ID INT NOT NULL
PRIMARY KEY ,
ParentID INT NOT NULL ,
ChildNumber INT NOT NULL
) ;
Next, let us populate them with 512K rows each:
DECLARE @adder INT ;
SET @adder = 1 ;
INSERT INTO dbo.Parent
( ID, ParentNumber )
VALUES ( 1, 1 ) ;
WHILE @adder < 500000
BEGIN ;
INSERT INTO dbo.Parent
( ID ,
ParentNumber
)
SELECT ID + @adder ,
ParentNumber
FROM dbo.Parent ;
SET @adder = @adder * 2 ;
END ;
GO
INSERT INTO dbo.Child
( ID, ParentID, ChildNumber )
SELECT ID, -- ID - int
ID, -- ParentID - int
0 -- ChildNumber - int
FROM dbo.Parent ;
We want to compare the performance of two queries without the overhead of retrieveing and or materializing result sets, so I will make sure the result sets are empty. The correlated subquery approach is as follows:
SET STATISTICS IO ON ;
SET STATISTICS TIME ON ;
GO
SELECT ChildNumber ,
ParentNumber
INTO #t1
FROM ( SELECT ChildNumber ,
( SELECT ParentNumber
FROM dbo.Parent AS p
WHERE p.ID = c.ParentID
) AS ParentNumber
FROM dbo.Child AS c
) AS t
-- no rows meet this criteria
WHERE ChildNumber + ParentNumber = -123 ;
The execution costs are:
Table 'Child'. Scan count 9, logical reads 2502, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Parent'. Scan count 9, logical reads 2215, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
The equivalent outer join looks like this (run it in another tab):
SET STATISTICS IO ON ;
SET STATISTICS TIME ON ;
GO
SELECT ChildNumber ,
ParentNumber
INTO #t2
FROM ( SELECT ChildNumber ,
ParentNumber
FROM dbo.Child AS c
LEFT OUTER JOIN dbo.Parent AS p ON p.ID = c.ParentID
) AS t
-- no rows meet this criteria
WHERE ChildNumber + ParentNumber = -123 ;
Its real execution costs are the same.
More to the point, when I hit Ctrl+L in both tabs, I see the same execution plan.
Conclusion
As we have seen, correlated subqueries do not have to execute once per each row. In fact, they are just macros - the optimizer can flatten them out and rewrite the queries involving them as outer joins.