What use of Cross apply?

Question

.

Rajkiran Swain · Answer

See the article in my blog for detailed performance comparison: INNER JOIN vs. CROSS APPLY CROSS APPLY works better on things that have no simple JOIN condition. This one selects 3 last records from t2 for each record from t1 : SELECT t1 .* t2o .* FROM t1 CROSS APPLY ( SELECT TOP 3 * FROM t2 WHERE t2 . t1_id = t1 . id ORDER BY t2 . rank DESC ) t2o It cannot be easily formulated with an INNER JOIN condition. You could probably do something like that using CTE 's and window function: WITH t2o AS ( SELECT t2 .* ROW_NUMBER () OVER ( PARTITION BY t1_id ORDER BY rank ) AS rn FROM t2 ) SELECT t1 .* t2o .* FROM t1 INNER JOIN t2o ON t2o . t1_id = t1 . id AND t2o . rn 3  but this is less readable and probably less efficient. Update: Just checked. master is a table of about 20000000 records with a PRIMARY KEY on id . This query: WITH q AS ( SELECT * ROW_NUMBER () OVER ( ORDER BY id ) AS rn FROM master ) t AS ( SELECT 1 AS id UNION ALL SELECT 2 ) SELECT * FROM t JOIN q ON q . rn t . id runs for almost 30 seconds while this one: WITH t AS ( SELECT 1 AS id UNION ALL SELECT 2 ) SELECT * FROM t CROSS APPLY ( SELECT TOP ( t . id ) m .* FROM master m ORDER BY id ) q is instant.

Sundar · Answer

CROSS APPLY works better on things that have no simple JOIN condition. This one selects 3 last records from t2 for each record from t1: SELECT t1.* t2o.* FROM t1 CROSS APPLY ( SELECT TOP 3 * FROM t2 WHERE t2.t1_id = t1.id ORDER BY t2.rank DESC ) t2o It cannot be easily formulated with an INNER JOIN condition. You could probably do something like that using CTE's and window function: WITH t2o AS ( SELECT t2.* ROW_NUMBER() OVER (PARTITION BY t1_id ORDER BY rank) AS rn FROM t2 ) SELECT t1.* t2o.* FROM t1 INNER JOIN t2o ON t2o.t1_id = t1.id AND t2o.rn  but this is less readable and probably less efficient. Update: Just checked. master is a table of about 20000000 records with a PRIMARY KEY on id. This query: WITH q AS ( SELECT * ROW_NUMBER() OVER (ORDER BY id) AS rn FROM master ) t AS ( SELECT 1 AS id UNION ALL SELECT 2 ) SELECT * FROM t JOIN q ON q.rn runs for almost 30 seconds while this one: WITH t AS ( SELECT 1 AS id UNION ALL SELECT 2 ) SELECT * FROM t CROSS APPLY ( SELECT TOP (t.id) m.* FROM master m ORDER BY id ) q is instant.