How does a subquery work in sql server 2012?

Question

I have created one temp table and inserted 10 records into it. The query against the table gets the 3rd highest salary. This query has a sub query. I want to know which query executes first: the inner query or outer query? Kindly check the query which is written below.

CREATE TABLE #tmpTbl
(
id int,
sal money
)


insert into #tmpTbl values('1','12000')
insert into #tmpTbl values('1','22000')
insert into #tmpTbl values('1','50000')
insert into #tmpTbl values('1','100000')
insert into #tmpTbl values('1','100')
insert into #tmpTbl values('1','5000')
insert into #tmpTbl values('1','15000')
insert into #tmpTbl values('1','18000')
insert into #tmpTbl values('1','19000')
insert into #tmpTbl values('1','21000')

SELECT * 
FROM #tmpTbl 
order by sal asc

SELECT DISTINCT sal 
FROM #tmpTbl t1 
WHERE 3 = (SELECT COUNT(DISTINCT SAL) 
           FROM #tmpTbl t2 
           WHERE t1.SAL <= t2.SAL)

Kindly run both queires. The first query will give you salary in ascending order and second will give you 22000.00.

Which runs first: the outer query or the inner query?

Answer 1

Based on your question you seem to be under the impression that the entire main query or the entire subquery will execute first. That isn't always possible or beneficial from a query performance point of view. The subquery that you have is a correlated one without an equality predicate. As far as I know it is not currently possible for SQL Server to evaluate that entire subquery as a first step. You can get a hint of the underlying issue if you issue a query hint to force a hash or merge join:

Msg 8622, Level 16, State 1, Line 26

Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

With the data and query that you have, one possible plan involves an index spool:

The table in the subquery is scanned once and transformed into a different table structure in tempdb to improve performance. The object in tempdb is accessed once per row in t1. In this case t1 is accessed first but it's not as if that entire part of the query executes before the subquery executes.

You can also get one scan of the subquery per row in t1:

Again, in this case t1 is accessed first but it's not as if that entire part of the query executes before the subquery executes.

If I add a lot of duplicate data to the temp table I get another plan shape:

You might think based on the sort that occurs before the nested loop join that the entire outer query executes before the inner query. That is not true. There is a scan done of the inner table for every unique value in t1. The sort is there just to optimize access of the table spool. The DISTINCT in the plan is implemented in the stream aggregate on the far left of the plan.

Answer 2

The answer is that they run in parallel (although the sub-query starts first, according to the execution plan) and then are merged to give you the result you want.

You can see this for yourself if you select the button to display either the estimated execution plan or the actual execution plan. The last item is your query. The execution plan is read from bottom right to upper left.

But as a bonus, there is a better way to get that answer, and will allow you to get the answer for any Nth highest salary. The query below uses the temp table you have defined above. It uses ROW_NUMBER() with the PARTITION and ORDER BY clauses to rank the salary per ID.

;WITH CTE_Temp AS
    (
    SELECT id
        , sal
        , SalRank = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY sal DESC)
    FROM #tmpTbl
    )
SELECT * FROM CTE_Temp WHERE SalRank = 3
;