How does a subquery work in sql server 2012? 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 &lt;= 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?
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
;