CROSS APPLY and OUTER APPLY in SQL Server

APPLY runs the right side ONCE PER ROW of the left side, and the right side can USE COLUMNS of the left row (a JOIN cannot do that).

CROSS APPLY : like INNER JOIN -> left row removed if right side returns nothing
OUTER APPLY : like LEFT  JOIN -> left row kept, right columns = NULL

Common uses:

• TOP N per group
• Calling a table-valued function per row
• Splitting strings (STRING_SPLIT) / reading JSON (OPENJSON)
• Reusing calculated columns (CROSS APPLY (SELECT ... ) as alias)
• Unpivoting with VALUES

Run the sample database script first. Every query below was run on that data, and the Output under each query is the real result.

USE SqlLearningDb;
GO

Topic table: Articles with comma separated Tags and JSON attributes

DROP TABLE IF EXISTS dbo.Articles;

CREATE TABLE dbo.Articles
(
    ArticleID   INT PRIMARY KEY,
    Title       VARCHAR(100) NOT NULL,
    AuthorID    INT NOT NULL,                 -- Employees.EmployeeID
    Tags        VARCHAR(200) NULL,            -- 'sql,database,tips'
    Attributes  NVARCHAR(MAX) NULL            -- JSON
);

INSERT INTO dbo.Articles (ArticleID, Title, AuthorID, Tags, Attributes) VALUES
(1, 'Getting started with SQL Server', 14, 'sql,database,beginner',
    N'{"views": 1520, "likes": 230, "level": "Beginner", "readMinutes": 6}'),
(2, 'Window functions explained',      14, 'sql,analytics,window-functions',
    N'{"views": 980,  "likes": 175, "level": "Intermediate", "readMinutes": 12}'),
(3, 'Python for data engineers',       12, 'python,etl,data-engineering',
    N'{"views": 2100, "likes": 410, "level": "Intermediate", "readMinutes": 15}'),
(4, 'Index tuning checklist',          15, 'sql,performance,indexes',
    N'{"views": 760,  "likes": 98,  "level": "Advanced", "readMinutes": 9}'),
(5, 'Company holiday list',            16, NULL,
    N'{"views": 300,  "likes": 12,  "level": "General"}');
GO

-- A table-valued function used later (returns the last N orders of a customer)
CREATE OR ALTER FUNCTION dbo.fn_LastOrders (@CustomerID INT, @N INT)
RETURNS TABLE
AS
RETURN
(
    SELECT TOP (@N) o.OrderID, o.OrderDate, o.Status
    FROM dbo.Orders o
    WHERE o.CustomerID = @CustomerID
    ORDER BY o.OrderDate DESC, o.OrderID DESC
);
GO

(5 rows affected)

Part A: TOP N per Group

Latest 2 orders for every customer (CROSS APPLY drops customers with no orders)

SELECT c.CustomerID, c.CustomerName, lo.OrderID, lo.OrderDate
FROM dbo.Customers c
CROSS APPLY (
                SELECT TOP (2) o.OrderID, o.OrderDate
                FROM dbo.Orders o
                WHERE o.CustomerID = c.CustomerID          -- uses the outer row!
                ORDER BY o.OrderDate DESC, o.OrderID DESC
            ) AS lo
ORDER BY c.CustomerID, lo.OrderDate DESC;

Same with OUTER APPLY: customers 18, 19, 20 appear with NULLs

SELECT c.CustomerID, c.CustomerName, lo.OrderID, lo.OrderDate
FROM dbo.Customers c
OUTER APPLY (
                SELECT TOP (2) o.OrderID, o.OrderDate
                FROM dbo.Orders o
                WHERE o.CustomerID = c.CustomerID
                ORDER BY o.OrderDate DESC, o.OrderID DESC
            ) AS lo
ORDER BY c.CustomerID;

Top 2 most expensive products in each category

SELECT cat.CategoryName, tp.ProductName, tp.UnitPrice
FROM dbo.Categories cat
CROSS APPLY (
                SELECT TOP (2) p.ProductName, p.UnitPrice
                FROM dbo.Products p
                WHERE p.CategoryID = cat.CategoryID
                ORDER BY p.UnitPrice DESC
            ) AS tp
ORDER BY cat.CategoryName, tp.UnitPrice DESC;

Best-selling product of each sales person (by revenue)

SELECT e.FirstName, best.ProductName, best.Revenue
FROM dbo.Employees e
CROSS APPLY (
                SELECT TOP (1) p.ProductName, SUM(oi.LineTotal) AS Revenue
                FROM dbo.Orders o
                JOIN dbo.OrderItems oi ON oi.OrderID = o.OrderID
                JOIN dbo.Products p    ON p.ProductID = oi.ProductID
                WHERE o.EmployeeID = e.EmployeeID
                GROUP BY p.ProductName
                ORDER BY Revenue DESC
            ) AS best;

Part B: APPLY with a Table-valued Function

(You CANNOT pass a column to a function in a normal JOIN)

SELECT c.CustomerName, f.OrderID, f.OrderDate, f.Status
FROM dbo.Customers c
CROSS APPLY dbo.fn_LastOrders(c.CustomerID, 3) AS f
WHERE c.City = 'Hyderabad';

Part C: Aggregates per Row (instead of many correlated subqueries)

SELECT c.CustomerName, s.TotalOrders, s.FirstOrder, s.LastOrder, s.Revenue
FROM dbo.Customers c
OUTER APPLY (
                SELECT COUNT(DISTINCT o.OrderID) AS TotalOrders,
                       MIN(o.OrderDate)          AS FirstOrder,
                       MAX(o.OrderDate)          AS LastOrder,
                       SUM(oi.LineTotal)         AS Revenue
                FROM dbo.Orders o
                JOIN dbo.OrderItems oi ON oi.OrderID = o.OrderID
                WHERE o.CustomerID = c.CustomerID
            ) AS s
ORDER BY s.Revenue DESC;

Part D: Reuse Calculated Columns (avoid repeating long expressions)

SELECT p.ProductName, calc.Margin, calc.MarginPct, grade.MarginGrade
FROM dbo.Products p
CROSS APPLY (SELECT p.UnitPrice - p.CostPrice                                     AS Margin,
                    CAST(100.0 * (p.UnitPrice - p.CostPrice) / p.UnitPrice AS DECIMAL(5,2)) AS MarginPct) AS calc
CROSS APPLY (SELECT CASE WHEN calc.MarginPct >= 35 THEN 'High'
                         WHEN calc.MarginPct >= 20 THEN 'Medium'
                         ELSE 'Low' END AS MarginGrade) AS grade
ORDER BY calc.MarginPct DESC;

Part E: STRING_SPLIT and Openjson with APPLY

Split tags into rows (CROSS APPLY drops the article with NULL tags)

SELECT a.ArticleID, a.Title, LTRIM(t.value) AS Tag
FROM dbo.Articles a
CROSS APPLY STRING_SPLIT(a.Tags, ',') AS t;

Count articles per tag

SELECT LTRIM(t.value) AS Tag, COUNT(*) AS Articles
FROM dbo.Articles a
CROSS APPLY STRING_SPLIT(a.Tags, ',') AS t
GROUP BY LTRIM(t.value)
ORDER BY Articles DESC, Tag;

Read JSON attributes into columns (openjson with schema)

SELECT a.Title, j.Views, j.Likes, j.Level, j.ReadMinutes
FROM dbo.Articles a
CROSS APPLY OPENJSON(a.Attributes)
     WITH ( Views       INT         '$.views',
            Likes       INT         '$.likes',
            Level       VARCHAR(20) '$.level',
            ReadMinutes INT         '$.readMinutes' ) AS j
ORDER BY j.Views DESC;

Part F: UNPIVOT with CROSS APPLY (VALUES)

SELECT e.FirstName, v.DateType, v.TheDate
FROM dbo.Employees e
CROSS APPLY (VALUES ('Hire Date',          e.HireDate),
                    ('1st Work Anniversary', DATEADD(YEAR, 1, e.HireDate)),
                    ('5th Work Anniversary', DATEADD(YEAR, 5, e.HireDate))) AS v(DateType, TheDate)
WHERE e.EmployeeID <= 3
ORDER BY e.FirstName, v.TheDate;

Practice Questions

1. For each department, show the 2 most recently hired employees (CROSS APPLY + TOP).
2. For each product, show the most recent order date that included it (OUTER APPLY, keep never-ordered products).
3. Write an inline TVF fn_TopProductsByCategory(@CategoryID, @N) and call it with CROSS APPLY for every category.
4. List articles whose JSON 'likes' is more than 15% of 'views'.
5. Use CROSS APPLY to compute order total, tax (18%), and grand total in a readable way.