CODEWITHSUNDEEP
sqlsql-serversql-server-2014
Imran Qadir Baksh - Baloch
Imran Qadir Baksh - Baloch

Reputation: 34064

Convert OUTER APPLY to LEFT JOIN

We have query which is slow in production(for some internal reason),

SELECT T2.Date
FROM Table1 T1
OUTER APPLY 
(
    SELECT TOP 1 T2.[DATE] 
    FROM Table2 T2 
    WHERE T1.Col1 = T2.Col1 
    AND T1.Col2 = T2.Col2 
    ORDER BY T2.[Date] DESC
) T2

But when I convert to LEFT JOIN it become fast,

SELECT Max(T2.[Date])
FROM Table1 T1
LEFT JOIN Table2 T2
   ON T1.Col1 = T2.Col1 
   AND T1.Col2 = T2.Col2
GROUP BY T1.Col1, T1.Col2

Can we say that both queries are equal? If not then how to convert it properly.

Upvotes: 1

Views: 1862

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

The queries are not exactly the same. It is important to understand the differences.

If t1.col1/t1.col2 are duplicated, then the first query returns a separate row for each duplication. The second combines them into a single row.

If either t1.col1 or t1.col2 are NULL, then the first query will return NULL for the maximum date. The second will return a row and the appropriate maximum.

That said, the two queries should have similar performance, particularly if you have an index on table2(col1, col2, date). I should note that under some circumstances the apply method is faster than joins, so relative performance depends on circumstances.

Upvotes: 1

Related Questions