Skip to content
Tutorials Center
My learning

Topics

Lesson 15 of 39 ยท Joins, CTEs and Window Functions

CROSS JOIN, Self Joins and APPLY in T-SQL

Learn CROSS JOIN for combinations, self joins for hierarchies, and T-SQL's CROSS APPLY and OUTER APPLY to run a correlated table expression per row, such as top N per group.

The previous lesson covered joins that match rows by a key. This lesson covers three other ways to combine rows. A cross join pairs every row with every other row. A self join joins a table to itself, which models hierarchies such as employees and managers. And APPLY, a T-SQL specialty, runs a table-valued expression once for each row of another table, which unlocks "top N per group" queries and calling table-valued functions per row.

CROSS JOIN

A cross join returns the Cartesian product: every row of A paired with every row of B. With 3 sizes and 4 colors you get 12 combinations. There is no ON clause.

SQL
SELECT s.size, c.color
FROM (VALUES (N'S'), (N'M'), (N'L')) AS s(size)
CROSS JOIN (VALUES (N'red'), (N'green'), (N'blue'), (N'black')) AS c(color);

Typical uses: generating all combinations (product variants), building a calendar or numbers grid, and pairing every row with a one-row parameter table. The danger is size: 10,000 rows cross joined to 10,000 rows is 100 million rows. If you wrote a join and forgot the ON, newer syntax makes it a syntax error, but a mistaken comma-separated FROM a, b without a WHERE silently produces a cross join. Prefer the explicit JOIN ... ON syntax.

SQL All size and color combinations

Press Run (or Ctrl + Enter) to see the result here.

Self joins

A self join treats one table as two, by giving it two aliases. The standard example is a table that points to itself: employees.manager_id references employees.id.

SQL
SELECT e.first_name AS employee,
       m.first_name AS manager
FROM dbo.employees AS e
LEFT JOIN dbo.employees AS m ON m.id = e.manager_id;

Use a LEFT JOIN so top-level people (with no manager) are not lost. Self joins are also how you compare rows within the same table: find pairs of customers living in the same city (a.city = b.city AND a.id < b.id, where a.id < b.id prevents mirrored pairs and self-pairs).

SQL Employees and their managers ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

SQL Pairs in the same city ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

For hierarchies of unknown depth (the whole chain of command), a self join only goes one level; use a recursive CTE (next lessons).

APPLY: a correlated table expression per row

APPLY evaluates a table expression (a subquery or a table-valued function) for each row of the left side, and that expression may refer to the left row's columns. There are two forms:

  • CROSS APPLY works like an inner join: left rows whose right expression returns no rows are dropped.
  • OUTER APPLY works like a left join: those left rows are kept with NULLs.

A normal JOIN cannot reference columns of the other side inside a derived table; APPLY can. That makes it perfect for top N per group:

SQL
-- The latest two orders for every customer
SELECT c.first_name, c.last_name, t.id AS order_id, t.ordered_at
FROM dbo.customers AS c
CROSS APPLY (
    SELECT TOP (2) o.id, o.ordered_at
    FROM dbo.orders AS o
    WHERE o.customer_id = c.id          -- refers to the outer row
    ORDER BY o.ordered_at DESC
) AS t;

With OUTER APPLY customers without any orders still appear, with NULL in the order columns. SQLite lacks APPLY, so the editor uses a window function to get the same top-N result, labelled as the stand-in:

SQL Stand-in: top 2 orders per customer without APPLY ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

APPLY with functions and computed columns

APPLY is also how you call an inline table-valued function per row, or reuse a calculation without repeating it. A neat trick: CROSS APPLY (SELECT price * quantity AS line_total) AS x lets later columns and the WHERE clause reuse x.line_total, because the applied expression is evaluated before them:

SQL
SELECT i.id, x.line_total
FROM dbo.order_items AS i
CROSS APPLY (SELECT i.unit_price * i.quantity AS line_total) AS x
WHERE x.line_total > 100;

STRING_SPLIT and OPENJSON are table-valued functions that are normally used with CROSS APPLY to explode a column of lists or JSON into rows.

SQL Stand-in: reuse a computed value ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Which one when?

Need Use
All combinations CROSS JOIN
Match rows by a key INNER / LEFT JOIN
Rows related to other rows in the same table self join
Top N per group, or call a table function per row CROSS APPLY / OUTER APPLY
Unknown-depth hierarchy recursive CTE

Common mistakes

  • Accidental cross join from a missing ON or WHERE condition.
  • Self join without a distinct alias (every column becomes ambiguous).
  • Mirrored duplicates in pair queries. Add a.id < b.id.
  • Forgetting ORDER BY inside TOP in an APPLY; the "top" rows are then arbitrary.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. How many rows does a CROSS JOIN of a 5-row table and a 7-row table return?

    A Cartesian product returns rows in A times rows in B, so 5 x 7 = 35.
  2. 2. Why does an employee-manager self join usually use LEFT JOIN?

    An inner join would discard rows whose manager_id is NULL.
  3. 3. What can CROSS APPLY do that a normal JOIN to a derived table cannot?

    APPLY evaluates the right expression once per left row and lets it reference that row, enabling top N per group.
  4. 4. What is the difference between CROSS APPLY and OUTER APPLY?

    OUTER APPLY behaves like a left join; CROSS APPLY like an inner join.

Summary

  • CROSS JOIN returns every combination; watch the row count.
  • A self join uses two aliases of one table; use LEFT JOIN for hierarchies with top nodes.
  • CROSS APPLY and OUTER APPLY run a correlated table expression per row: top N per group, per-row functions, reusable calculations.
  • For arbitrary-depth hierarchies, use a recursive CTE.

Next: Set Operators and Subqueries