Quiz: CROSS APPLY

Quiz: CROSS APPLY in TSQL

Table definitions and content:

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
  • SELECT COUNT(*) AS ct
    FROM dbo.t1
    CROSS APPLY (SELECT 1 / 0 AS x
         FROM dbo.t2
         WHERE t1.t1c1 = t2.t1c1) AS t2;
  • 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;
  • 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;
  • SELECT t1c1 * 2 AS d
    FROM dbo.t2;

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

What details on the answers?

Read about the logic behind the answers in this blog post.

4 thoughts on “Quiz: CROSS APPLY

  1. Very Interesting. Thanks for the demos

    1. Glad you enjoyed it!

  2. 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.

    1. Great question. I’m going to summarize in my own words, and then link to someone else’s code examples 🙂

      One thing it’s fantastic for that isn’t in this quiz is calling table valued functions. I didn’t include those just 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.

      Another thing I might use it for is when a query needs a correlated subquery — somewhat like an inline function.

      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.

      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

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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.