CROSS APPLY in T-SQL: Why to APPLY

Michelle asked a great question:

In your own words, why would one want to use a cross apply operator rather than a join operator? I’m old school, and I’m just not getting why a cross apply would be so much better to use than a join.

Here’s my top 3 favorite uses for CROSS APPLY and OUTER APPLY:

1. APPLY is fantastic for calling table valued functions. I didn’t include questions about those in the quiz, simply for the purposes of keeping the code simple, and because I wanted the quiz to be about thinking through how apply works — but it’s still the #1 use.
2. Another thing I might use it for is when a query needs a correlated subquery — somewhat like an inline function.
3. And I also like it for queries that have a calculation that needs to be done and which is referenced in multiple columns in the query, or perhaps also in a predicate and the select. You can perform the computation once in the apply and then reference it multiple times. That way if you have to change the formula later on, you only have to change it in once place, plus I find it’s easier to read in some cases.

But it can do even more…

It can help you (un) pivot data. Check out Kenneth Fisher’s example here.

Brad Schulz’s post here has code samples for those, plus more things like showing how it can be useful for shredding XML: http://bradsruminations.blogspot.com/2011/04/t-sql-tuesday-017-it-slices-it-dices-it.html

Brad gets a bit jokey at the end of the post, but there’s a lot of valid uses along the way.

Quiz Results/Answers/Explanations: CROSS APPLY

The questions in this quiz are based on two tables, created and populated with the following commands:

CREATE TABLE dbo.t1 (t1c1 int IDENTITY PRIMARY KEY); 
CREATE TABLE dbo.t2 (t2c1 int IDENTITY PRIMARY KEY, t1c1 int NOT NULL DEFAULT 'foo'); 
GO

INSERT dbo.t1 DEFAULT VALUES
GO 5
INSERT dbo.t2 (t1c1) SELECT TOP (2) t1c1 FROM dbo.t1;
GO

This produces two tables which look like this:

Question 1: If you run this code in a SQL Server Management Studio session, what will appear in the Messages window?

SELECT COUNT(*) AS ct 
FROM dbo.t1
CROSS APPLY (SELECT 1 / 0 AS x 
     FROM dbo.t2 
     WHERE t1.t1c1 = t2.t1c1) AS t2;

Answer: The results of COUNT(*) based on an inner join between the two tables

• Correct: 106 (42%)
• Incorrect: 146 (58%)

I know, it’s weird, there’s a 1/0 in there which seems like it would produce a divide by zero error! But that bit of code never gets evaluated in this case, and the query runs successfully.

Sometimes a query using APPLY can be “transformed” into a join operation, and this is one of those cases. You can read more about this in Paul White’s article here.

Most of the time, if you want a join, you should use a join. However, it’s definitely useful to know that APPLY can be used this way for when you are reading and editing code written by others.

Question 2: Will these queries return the same result?

SELECT COUNT(*) AS ct
FROM dbo.t1
   JOIN dbo.t2
    ON t1.t1c1 = t2.t1c1;

SELECT COUNT(*) AS ct
FROM dbo.t1
CROSS APPLY (SELECT 1 / 0 AS x 
   FROM dbo.t2 
   WHERE t1.t1c1 = t2.t1c1) AS t2;

Answer: yes

• Correct: 140 (56%)
• Incorrect: 112 (44%)

In this case, the second query will be rewritten to use an inner join, just like the first query. One method you can use to verify this is to look at the execution plan for both queries (and you can obviously run them and compare the results).

Both queries return a count of 2.

Here are the estimated plans for both queries:

Question 3: Will these queries return the same result?

SELECT COUNT(*) AS ct
FROM dbo.t1
   JOIN dbo.t2
    ON t1.t1c1 = t2.t1c1;

SELECT COUNT(*) AS ct
FROM dbo.t1
OUTER APPLY (SELECT 1 / 0 AS x 
   FROM dbo.t2 
   WHERE t1.t1c1 = t2.t1c1) AS t2;

Answer: no

• Correct: 198 (79%)
• Incorrect: 54 (21%)

The first query returns the count of 2. The second query returns a count of 5.

This is because the first query is implemented as an inner join, and the second query is implemented as an outer join.

Question 4: Will these queries return the same result?

SELECT t1c1 * 2 AS d
FROM dbo.t2;

SELECT x.d
FROM dbo.t2
CROSS APPLY (SELECT t1c1 * 2 AS d) AS x;

Answer: yes

• Correct: 161 (64%)
• Incorrect: 91 (36%)

This is an example of using a CROSS APPLY to compute a calculation.

It’s pretty trivial in this case, but I find this can be very useful in the case of complex queries where you refer to the result of a calculation more than once – doing this can allow you to only do the computation in once place and then refer to it many times. Not only can this make the query more readable sometimes, it can limit the errors if you need to change the calculations (because you’re changing it in fewer places.)