Advanced SQL & Data Engineering • Training 03

Article-Training • SQL Server–Friendly Series

CROSS APPLY / OUTER APPLY

Let the Right Side Depend on Each Row from the Left

APPLY is one of SQL Server's most useful advanced operators when the right-side logic must run in the context of each row from the left.

Left Row → Run Right-Side Logic → Return Related Rows → Keep or Drop Left Row

Customer

→

APPLY

→

Latest Order

Product

→

APPLY

→

Top 3 Sales

8learning modules

24interactive practices

5rapid review questions

50%certificate threshold

Participant name

Learning target

Use CROSS APPLY and OUTER APPLY to solve row-dependent SQL Server problems clearly, safely and efficiently.

Practice progress0 / 24

Complete 12 of 24 practices (50%) and enter your name to unlock the Certificate of Participation.

MODULE 01

🧠

Why APPLY Exists

Traditional joins work beautifully when both sides can be related with a normal join predicate. APPLY becomes valuable when the right side must reference columns from the current row on the left.

👁️

Think row by row

Imagine asking a different question for each customer: 'What is this customer's latest order?' The right-side query needs that customer's ID before it can answer.

Core ideas

The right side may reference columns from the left row
APPLY returns a table expression, not just one scalar value
The pattern is correlated by design
Use APPLY when row context makes the query clearer

Basic mental model

SELECT c.CustomerID, x.*
FROM dbo.Customers AS c
CROSS APPLY
(
    -- This query can use c.CustomerID
    SELECT ...
) AS x;

✅

APPLY is best understood as: take one left row, run a table expression that can see that row, then combine the results.

Practice — 3 cases

Practice 1 / Práctica 1

When is APPLY especially useful?

AWhen the right-side query needs values from the current left-side row.BOnly when two tables have no relationship at all.COnly to replace every INNER JOIN.

Best answer: A. When the right-side query needs values from the current left-side row.

Practice 2 / Práctica 2

What is the key mental model for APPLY?

AThe right side is executed once for the entire database.BThe left side is always deleted after the query.CFor each left row, evaluate the right-side table expression in that row's context.

Best answer: C. For each left row, evaluate the right-side table expression in that row's context.

Practice 3 / Práctica 3

Which SQL Server operators implement this pattern?

AINTERSECT and EXCEPT.BCROSS APPLY and OUTER APPLY.CPIVOT and UNPIVOT only.

Best answer: B. CROSS APPLY and OUTER APPLY.

MODULE 02

⚡

CROSS APPLY: Keep Only Rows with a Result

CROSS APPLY returns a left row only when the right-side expression returns at least one row. That makes it ideal when 'no related result' should mean 'exclude this left row.'

👁️

Like an INNER JOIN with row-dependent logic

If a customer has no qualifying order, CROSS APPLY does not produce a row for that customer.

Core ideas

Zero right-side rows means the left row is removed
One right-side row gives one combined result row
Many right-side rows can multiply the left row
Use TOP when you intentionally want only one related row

Latest qualifying order

SELECT c.CustomerID,
       x.OrderID,
       x.OrderDate
FROM dbo.Customers AS c
CROSS APPLY
(
    SELECT TOP (1)
           o.OrderID,
           o.OrderDate
    FROM dbo.Orders AS o
    WHERE o.CustomerID = c.CustomerID
    ORDER BY o.OrderDate DESC, o.OrderID DESC
) AS x;

✅

CROSS APPLY is a strong choice when a missing right-side result should remove the left entity from the answer.

Practice — 3 cases

Practice 4 / Práctica 4

What happens in CROSS APPLY when the right side returns zero rows?

AThe left row is returned with NULLs.BThat left row disappears from the result.CSQL Server inserts a placeholder row.

Best answer: B. That left row disappears from the result.

Practice 5 / Práctica 5

CROSS APPLY is most similar in row-preservation behavior to which join?

AINNER JOIN.BFULL OUTER JOIN.CCROSS JOIN regardless of matches.

Best answer: A. INNER JOIN.

Practice 6 / Práctica 6

Why might CROSS APPLY be preferable to repeating the same correlated subquery several times?

ABecause APPLY automatically creates indexes.BBecause it guarantees zero logical reads.CBecause it can centralize row-dependent logic and expose multiple columns once.

Best answer: C. Because it can centralize row-dependent logic and expose multiple columns once.

MODULE 03

🛟

OUTER APPLY: Preserve the Left Row

OUTER APPLY uses the same row-dependent idea as CROSS APPLY, but it preserves the left row when the right side returns nothing. The missing right-side columns become NULL.

👁️

Like a LEFT JOIN with row-dependent logic

A customer with no orders still appears in the report. OrderID and OrderDate are simply NULL.

Core ideas

Preserves every left row
Returns NULLs when the right side has no row
Useful for completeness-oriented reports
Choose based on business meaning, not habit

Keep customers with no orders

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

✅

The CROSS vs OUTER decision is fundamentally a business rule: should left rows with no right-side result disappear or remain?

Practice — 3 cases

Practice 7 / Práctica 7

What is the main difference between OUTER APPLY and CROSS APPLY?

AOUTER APPLY cannot reference the left row.BOUTER APPLY always returns every row from both tables.COUTER APPLY preserves the left row even when the right side returns nothing.

Best answer: C. OUTER APPLY preserves the left row even when the right side returns nothing.

Practice 8 / Práctica 8

If the right side returns nothing under OUTER APPLY, what appears in the right-side columns?

AZeros in every column.BNULL values.CThe previous row's values.

Best answer: B. NULL values.

Practice 9 / Práctica 9

Which operator fits a report that must list every customer, even customers with no orders?

AOUTER APPLY.BCROSS APPLY only.CINTERSECT.

Best answer: A. OUTER APPLY.

MODULE 04

🏁

Top-N Per Row: Latest, Best or Highest Priority

One of APPLY's strongest production patterns is asking a different Top-N question for every left-side entity: latest order per customer, newest status per employee, top three transactions per account, or highest-priority event per device.

👁️

The question changes with each row

For Customer 101, find Customer 101's latest order. For Customer 102, run the same logic using Customer 102's key. APPLY expresses that relationship directly.

Core ideas

TOP and ORDER BY define the winner for each left row
Use a tie-breaker such as a unique ID
TOP (N) can return several related rows per left row
Indexes on correlation and ordering columns often matter

Latest employee status

SELECT e.EmployeeID,
       s.StatusCode,
       s.EffectiveDate
FROM dbo.Employee AS e
OUTER APPLY
(
    SELECT TOP (1)
           h.StatusCode,
           h.EffectiveDate
    FROM dbo.EmployeeStatusHistory AS h
    WHERE h.EmployeeID = e.EmployeeID
    ORDER BY h.EffectiveDate DESC,
             h.StatusHistoryID DESC
) AS s;

✅

APPLY makes 'latest related row per entity' readable because the correlation and ranking logic stay together.

Practice — 3 cases

Practice 10 / Práctica 10

Why is TOP (1) with ORDER BY a common APPLY pattern?

AIt lets each left row retrieve its best, latest or highest-priority related row.BIt forces SQL Server to use exactly one CPU core.CIt converts every result into a temp table.

Best answer: A. It lets each left row retrieve its best, latest or highest-priority related row.

Practice 11 / Práctica 11

Why should the ORDER BY include a deterministic tie-breaker?

ATo make every query parallel.BSo tied rows do not produce an unpredictable winner.CTo prevent all NULL values.

Best answer: B. So tied rows do not produce an unpredictable winner.

Practice 12 / Práctica 12

Which is a classic APPLY use case?

ARenaming a database.BChanging server collation.CFinding each employee's latest status record.

Best answer: C. Finding each employee's latest status record.

MODULE 05

🧮

Reusable Row Calculations with APPLY + VALUES

APPLY is not limited to subqueries. VALUES can create a small row expression that calculates a value once, gives it a name and lets the rest of the query reuse it.

👁️

Name the calculation once

Instead of repeating Revenue - Cost in several CASE expressions and output columns, define GrossProfit once and reuse the alias.

Core ideas

Reduces repeated expressions
Can improve readability of layered calculations
Useful for business rules that feed several output columns
Keep expressions understandable; do not turn APPLY into a puzzle

Calculate once, reuse many times

SELECT s.SaleID,
       x.GrossProfit,
       CASE WHEN x.GrossProfit > 0
            THEN 'Profit'
            ELSE 'Loss'
       END AS ProfitFlag
FROM dbo.Sales AS s
CROSS APPLY
(
    VALUES (s.Revenue - s.Cost)
) AS x(GrossProfit);

✅

APPLY can act as a readability tool, not just a join-like operator.

Practice — 3 cases

Practice 13 / Práctica 13

What can APPLY with VALUES help you do?

ACreate a new database automatically.BCalculate a row-dependent expression once and reuse its alias.CReplace every GROUP BY.

Best answer: B. Calculate a row-dependent expression once and reuse its alias.

Practice 14 / Práctica 14

Why can reusable expressions improve maintainability?

ABecause they remove all execution costs.BBecause they prevent all data quality problems.CBecause one definition reduces repeated business logic in SELECT, CASE or filters.

Best answer: C. Because one definition reduces repeated business logic in SELECT, CASE or filters.

Practice 15 / Práctica 15

Which statement is accurate about APPLY and VALUES?

AVALUES can act as a tiny row-producing expression whose columns depend on the left row.BVALUES can only be used in INSERT statements.CAPPLY with VALUES permanently stores the result.

Best answer: A. VALUES can act as a tiny row-producing expression whose columns depend on the left row.

MODULE 06

🧩

APPLY with Table-Valued Functions

A table-valued function can behave like a parameterized table. APPLY lets each left row feed its own values into that function and receive a rowset back.

👁️

A reusable row-dependent table

For each employee, call a function that returns that employee's qualifying assignments. The left row supplies EmployeeID; the function returns rows.

Core ideas

Inline TVFs often compose well with SQL queries
Each left row can pass a different parameter
CROSS APPLY removes rows with no function output
OUTER APPLY preserves them with NULLs

Parameterized rowset

SELECT e.EmployeeID,
       a.AssignmentID,
       a.Hours
FROM dbo.Employee AS e
CROSS APPLY dbo.fn_OpenAssignments(e.EmployeeID) AS a;

✅

APPLY is the natural bridge when a reusable table expression needs parameters from each row.

Practice — 3 cases

Practice 16 / Práctica 16

What is a major reason APPLY works naturally with table-valued functions?

ATable-valued functions return only scalar numbers.BAPPLY disables function parameters.CA table-valued function can receive values from the current left row and return rows for that entity.

Best answer: C. A table-valued function can receive values from the current left row and return rows for that entity.

Practice 17 / Práctica 17

Which function type generally integrates most directly with APPLY?

AA table-valued function.BA database backup file.CA SQL Server Agent schedule.

Best answer: A. A table-valued function.

Practice 18 / Práctica 18

What should you still evaluate when using a function through APPLY?

AOnly the function's name length.BPerformance, cardinality behavior and whether the function design is appropriate.CWhether the server wallpaper matches the query.

Best answer: B. Performance, cardinality behavior and whether the function design is appropriate.

MODULE 07

📈

Performance: Read the Plan, Not the Syntax

APPLY can be elegant and fast, but syntax alone tells you nothing about cost. A row-dependent lookup may run efficiently with the right index—or become expensive when repeated for a large left input.

👁️

Think in repeated work

If 500,000 left rows each trigger a poorly supported lookup, a clean-looking query can still be expensive.

Core ideas

Inspect the actual execution plan
Measure logical reads and elapsed time
Index correlation and ordering columns when appropriate
Compare alternatives such as window functions for large sets

Index idea for latest order

CREATE INDEX IX_Orders_Customer_Date
ON dbo.Orders
(
    CustomerID,
    OrderDate DESC,
    OrderID DESC
)
INCLUDE (Amount, Status);

✅

Use APPLY because it expresses the problem well, then verify that the optimizer can execute that expression efficiently.

Practice — 3 cases

Practice 19 / Práctica 19

What should you inspect before assuming an APPLY query is fast?

AThe actual execution plan, row counts and logical reads.BOnly the query's indentation.COnly whether APPLY appears once or twice.

Best answer: A. The actual execution plan, row counts and logical reads.

Practice 20 / Práctica 20

Which index pattern may help a latest-row APPLY lookup?

AAn unrelated index on a text description column.BNo index can ever help APPLY.CAn index beginning with the correlation key and supporting the desired order.

Best answer: C. An index beginning with the correlation key and supporting the desired order.

Practice 21 / Práctica 21

Why can APPLY become expensive?

ABecause it always scans every database on the server.BBecause row-dependent work can be repeated many times when the left side is large or the right side is poorly indexed.CBecause APPLY cannot use predicates.

Best answer: B. Because row-dependent work can be repeated many times when the left side is large or the right side is poorly indexed.

MODULE 08

🏭

Production Decision Guide: APPLY vs JOIN vs Window Functions

APPLY is a tool, not a badge of sophistication. A standard JOIN may be simpler for ordinary relationships. A window function may be better for set-wide ranking. APPLY is strongest when the right-side table expression naturally depends on the current left row.

👁️

Choose the shape that matches the problem

The best SQL is not the query with the most advanced keyword. It is the query whose logic is easiest to understand, validate and operate at the required scale.

Core ideas

JOIN for ordinary set relationships
APPLY for row-dependent table expressions
Window functions for set-wide ranking and analytics
Compare execution plans when two designs are both reasonable

Decision sketch

Need right-side logic
that depends on each left row?
        |
       YES
        |
   APPLY candidate
   /             \
CROSS           OUTER
drop no-match   keep no-match

Otherwise:
consider JOIN or window functions.

✅

The production mindset is simple: use APPLY where it clarifies row-dependent logic, not merely because you can.

Practice — 3 cases

Practice 22 / Práctica 22

When should you prefer a normal JOIN over APPLY?

AWhenever the query contains a date column.BWhen both sides can be related cleanly with a standard set-based join and no row-dependent table expression is needed.CNever; APPLY should replace all joins.

Best answer: B. When both sides can be related cleanly with a standard set-based join and no row-dependent table expression is needed.

Practice 23 / Práctica 23

When might a window-function solution compete with APPLY?

AWhen ranking many related rows set-wise, especially across a large dataset.BOnly when there are no tables.COnly for CREATE DATABASE statements.

Best answer: A. When ranking many related rows set-wise, especially across a large dataset.

Practice 24 / Práctica 24

What is the best production rule for APPLY?

AUse it because it looks advanced.BAvoid it in every production system.CUse it when row-dependent logic is clearer, choose CROSS vs OUTER by business meaning, and validate performance.

Best answer: C. Use it when row-dependent logic is clearer, choose CROSS vs OUTER by business meaning, and validate performance.

5-Question Knowledge Check

Can you choose and use APPLY correctly?

Open each item after answering it in your own words. The 24 interactive practices above drive certificate progress.

1. What makes APPLY different from a normal independent derived table?

The right-side table expression can reference columns from the current row on the left.

2. What is the row-preservation difference between CROSS APPLY and OUTER APPLY?

CROSS APPLY drops a left row when the right side returns nothing; OUTER APPLY keeps it and returns NULLs on the right.

3. Why is APPLY often used with TOP (1) and ORDER BY?

To select the latest, best or highest-priority related row separately for each left-side entity.

4. What performance risk should you remember?

Row-dependent work can be repeated for many left rows, so indexes, cardinality, logical reads and the actual plan matter.

5. When should you not force APPLY?

When a normal JOIN or a set-based window-function solution expresses the problem more simply and performs appropriately.

APPLY Decision Map

A quick production reference

Need	Strong candidate
Right-side logic depends on each left row	APPLY
Exclude left rows with no right-side result	CROSS APPLY
Keep all left rows even with no right-side result	OUTER APPLY
Latest or Top-N related rows per entity	APPLY + TOP + ORDER BY
Ordinary relationship between two sets	JOIN

Certificate of Participation

Complete at least 12 of the 24 practice cases (50%) and enter your name.

0 / 24 • 0%

Production note

APPLY is most valuable when the right-side table expression truly depends on each left row.