SQL JOINs 2: Self Join, Non-Equi Join, Semi/Anti Join and CROSS APPLY vs LATERAL

Version note: The SQL (Structured Query Language) queries in this post were run on SQL Server 2025 (17.0, Cumulative Update 10); the dialect comparisons were run on PostgreSQL 18.6 and MySQL 8.4.

Guess First: What Is This Order's Total?

Order 372 has three books, and the customer paid in two parts: a 200 TRY (Turkish lira) gift card and the rest by card. We want the order's item total and the amount paid in a single query:

SELECT o.order_id,
       SUM(oi.quantity * oi.unit_price) AS items_total,
       SUM(p.amount) AS paid_total
FROM dbo.orders AS o
JOIN dbo.order_items AS oi ON oi.order_id = o.order_id
JOIN dbo.payments AS p ON p.order_id = o.order_id
WHERE o.order_id = 372
GROUP BY o.order_id;

We expect the two numbers to be equal, because in the seed data the payments of every paid order equal its item total. The output:

order_id  items_total  paid_total
--------  -----------  ----------
372       2263.00      3394.50

(1 rows affected)

The order's real total is 1,131.50 TRY. The query doubled the item total and tripled the amount paid. Each join is correct on its own; the bug is in having both together. In the last section of this post we take this fan-out apart. Before that we break the two assumptions from the previous post, that the join condition is always an equality and that we always combine two different tables: CROSS JOIN, self joins, non-equi joins, semi joins with EXISTS, anti joins with NOT EXISTS, and CROSS APPLY with the standard LATERAL.

CROSS JOIN

CROSS JOIN is a join without a condition: every row on the left matches every row on the right, and the result is the Cartesian product. What we got by accident in the previous post when we forgot the join condition in the comma syntax, we now ask for on purpose. A candidate list for recommending every Turkish book to every customer:

SELECT COUNT(*) AS combinations
FROM dbo.customers AS c
CROSS JOIN dbo.books AS b
WHERE b.language_code = 'tr';

combinations
------------
160

(1 rows affected)

40 customers × 4 books = 160 candidates. A real recommendation system removes what the customer already bought from this list with an anti join; we will see that shortly. The most common legitimate use of CROSS JOIN is producing a report skeleton: one row for every language and every order status, so combinations without data still show up with zero.

SELECT l.language_code, s.status
FROM (VALUES ('en'), ('tr')) AS l(language_code)
CROSS JOIN (VALUES ('Paid'), ('Shipped')) AS s(status)
ORDER BY l.language_code, s.status;

language_code  status
-------------  -------
en             Paid
en             Shipped
tr             Paid
tr             Shipped

(4 rows affected)

When you attach this skeleton to the real data with a LEFT JOIN, empty combinations stay in the report. The only thing to watch is size: a CROSS JOIN of two large tables produces as many rows as the product of their row counts.

Self Join: A Category and Its Parent

The categories table references itself: parent_category_id points to another row of the same table. To see a category's name next to its parent's name, we use the table twice with two different aliases:

SELECT child.category_id, child.name AS category, parent.name AS parent_category
FROM dbo.categories AS child
LEFT JOIN dbo.categories AS parent
    ON parent.category_id = child.parent_category_id
ORDER BY child.category_id;

category_id  category         parent_category
-----------  ---------------  ---------------
1            Fiction          NULL
2            Classics         Fiction
3            Science Fiction  Fiction
4            Dystopian        Science Fiction
5            Fantasy          Fiction
6            Mystery          Fiction
7            Poetry           Fiction
8            Non-Fiction      NULL
9            Science          Non-Fiction
10           Computing        Non-Fiction
11           Programming      Computing
12           Algorithms       Computing
13           Children         NULL

(13 rows affected)

The aliases are mandatory here: they are the only way to tell the two copies of the same table apart. The LEFT JOIN is deliberate too; with an INNER JOIN the three root categories without a parent (Fiction, Non-Fiction, Children) would disappear. To go up one more level, add another self join:

SELECT c.name AS category, p.name AS parent, gp.name AS grandparent
FROM dbo.categories AS c
JOIN dbo.categories AS p ON p.category_id = c.parent_category_id
JOIN dbo.categories AS gp ON gp.category_id = p.parent_category_id;

category     parent           grandparent
-----------  ---------------  -----------
Dystopian    Science Fiction  Fiction
Programming  Computing        Non-Fiction
Algorithms   Computing        Non-Fiction

(3 rows affected)

Writing one join per level works as long as you know the depth of the tree in advance. If the depth varies (a system where users can create unlimited subcategories), the right tool is a recursive CTE (Common Table Expression); in post 12 we turn this tree into a breadcrumb path. The design options for hierarchical data are in the Solution Pattern series post "SQL'de Hiyerarşik Veri: Adjacency List, Recursive CTE, Closure Table ve hierarchyid" (hierarchical data in SQL).

Non-Equi Join: Price Bands

A join condition does not have to be an equality; you can write any condition in ON. To put books into price bands, let's define the bands as a table and join with a range condition:

WITH price_bands AS (
    SELECT band, min_price, max_price
    FROM (VALUES ('budget',      0.00,  300.00),
                 ('standard',  300.00,  700.00),
                 ('premium',   700.00, 1000.00),
                 ('collector', 1000.00, 99999.99)) AS v(band, min_price, max_price)
)
SELECT pb.band, COUNT(b.book_id) AS book_count, MIN(b.list_price) AS cheapest, MAX(b.list_price) AS most_expensive
FROM price_bands AS pb
LEFT JOIN dbo.books AS b
    ON b.list_price >= pb.min_price
   AND b.list_price <  pb.max_price
GROUP BY pb.band, pb.min_price
ORDER BY pb.min_price;

band       book_count  cheapest  most_expensive
---------  ----------  --------  --------------
budget     14          200.00    290.90
standard   52          300.50    680.50
premium    4           890.00    950.00
collector  7           1150.00   1450.00

(4 rows affected)

14 + 52 + 4 + 7 = 77: every book landed in exactly one band, because the ranges were written with the half-open rule from post 6 (>= lower bound, < upper bound). With BETWEEN, a book at exactly 300.00 TRY would fall into two bands and the total would exceed 77. We did the same job as the CASE in post 7, but this time the bands are data: adding a band or moving a boundary changes a table, not the query. Non-equi joins are also handy for data checks. Asking "was any book ordered before its catalog listing date?" with the condition JOIN dbo.books AS b ON b.book_id = oi.book_id AND CAST(o.ordered_at AS DATE) < b.listed_on returns zero rows in Bookstore; proof that the data is consistent.

Semi Join: EXISTS

"Customers who bought at least one Programming book" is a semi join: we want the rows of the left table that have at least one match on the right, but not the right table's columns. Let's see what happens when we write it as a join (limited to the first three customers to keep the output short):

SELECT c.customer_id, c.first_name
FROM dbo.customers AS c
JOIN dbo.orders AS o ON o.customer_id = c.customer_id
JOIN dbo.order_items AS oi ON oi.order_id = o.order_id
JOIN dbo.books AS b ON b.book_id = oi.book_id
WHERE b.category_id = 11
  AND c.customer_id <= 3
ORDER BY c.customer_id;

16 rows come back: Ada 4 times, Alan 2 times, Grace 10 times. Every matching order item brings the customer back once more. As we saw in the previous post, the reflex is to add DISTINCT; the right tool is EXISTS:

SELECT c.customer_id, c.first_name
FROM dbo.customers AS c
WHERE EXISTS (
    SELECT 1
    FROM dbo.orders AS o
    JOIN dbo.order_items AS oi ON oi.order_id = o.order_id
    JOIN dbo.books AS b ON b.book_id = oi.book_id
    WHERE o.customer_id = c.customer_id
      AND b.category_id = 11
)
  AND c.customer_id <= 3
ORDER BY c.customer_id;

customer_id  first_name
-----------  ----------
1            Ada
2            Alan
3            Grace

(3 rows affected)

EXISTS asks whether a subquery returns at least one row; how many rows it returns does not matter, which is why we write SELECT 1. Every customer shows up at most once in the result. The row counts of the two forms over all customers: join 58, EXISTS 25. The difference is both correctness and work: the join with DISTINCT first produces 58 rows and then deduplicates, while EXISTS can stop searching for a customer as soon as it finds the first match. Since the subquery references c.customer_id from the outer query, it is a correlated subquery; we will cover that concept in detail in post 11.

Anti Join: NOT EXISTS and LEFT JOIN … IS NULL

An anti join is the opposite of a semi join: the left rows that have no match at all on the right. Let's ask for authors without books in the catalog in three ways:

SELECT
    (SELECT COUNT(*) FROM dbo.authors AS a
     WHERE NOT EXISTS (SELECT 1 FROM dbo.book_authors AS ba WHERE ba.author_id = a.author_id)) AS not_exists,
    (SELECT COUNT(*) FROM dbo.authors AS a
     LEFT JOIN dbo.book_authors AS ba ON ba.author_id = a.author_id
     WHERE ba.author_id IS NULL) AS left_join_null,
    (SELECT COUNT(*) FROM dbo.authors AS a
     WHERE a.author_id NOT IN (SELECT ba.author_id FROM dbo.book_authors AS ba)) AS not_in;

not_exists  left_join_null  not_in
----------  --------------  ------
2           2               2

(1 rows affected)

All three say 2: Octavia E. Butler and Toni Morrison are in the author list but have no books in the catalog. NOT IN gave the right result here because book_authors.author_id is part of the primary key and cannot be NULL. Had the subquery's column been nullable, a single NULL would have brought the whole result down to zero; we saw that with a fixed list in post 6 and will see it with a subquery in post 11. NOT EXISTS does not fall into this trap. And the choice between NOT EXISTS and LEFT JOIN … IS NULL? SQL Server produces the same plan for both:

SET SHOWPLAN_TEXT ON;
GO
SELECT a.author_id FROM dbo.authors AS a
WHERE NOT EXISTS (SELECT 1 FROM dbo.book_authors AS ba WHERE ba.author_id = a.author_id);
SELECT a.author_id FROM dbo.authors AS a
LEFT JOIN dbo.book_authors AS ba ON ba.author_id = a.author_id
WHERE ba.author_id IS NULL;
GO
SET SHOWPLAN_TEXT OFF;

  |--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([a].[author_id]))
       |--Clustered Index Scan(OBJECT:([sqlt_09].[dbo].[authors].[PK_authors] AS [a]))
       |--Top(TOP EXPRESSION:((1)))
            |--Clustered Index Scan(OBJECT:([sqlt_09].[dbo].[book_authors].[PK_book_authors] AS [ba]), WHERE:([sqlt_09].[dbo].[book_authors].[author_id] as [ba].[author_id]=[sqlt_09].[dbo].[authors].[author_id] as [a].[author_id]))

The plan is exactly this for both queries: Left Anti Semi Join. The optimizer recognised the intent of LEFT JOIN … IS NULL and turned it into an anti join. That is not guaranteed in every case; in more complex queries the LEFT JOIN may first produce all matches and filter afterwards. NOT EXISTS states the intent directly, has no NULL trap and shows the reader plainly that it asks an "absence" question. Make it your default.

CROSS APPLY, OUTER APPLY and LATERAL

"Every customer's last three orders" cannot be written with an ordinary join, because "last three" is a subquery that changes per customer. The first attempt usually looks like this:

SELECT c.customer_id, last3.order_id
FROM dbo.customers AS c
JOIN (
    SELECT TOP (3) o.order_id, o.ordered_at
    FROM dbo.orders AS o
    WHERE o.customer_id = c.customer_id
    ORDER BY o.ordered_at DESC
) AS last3 ON 1 = 1;

Msg 4104, Level 16, State 1, Server b8be943222fa, Line 6
The multi-part identifier "c.customer_id" could not be bound.

A derived table, that is, a subquery in FROM, cannot see the tables to its left; each is evaluated independently. The SQL:1999 standard introduced the LATERAL keyword for this: a lateral subquery sees the current row of the table to its left. The T-SQL equivalent is APPLY, available since SQL Server 2005:

SELECT c.customer_id, c.first_name, last3.order_id, last3.ordered_at
FROM dbo.customers AS c
CROSS APPLY (
    SELECT TOP (3) o.order_id, o.ordered_at
    FROM dbo.orders AS o
    WHERE o.customer_id = c.customer_id
    ORDER BY o.ordered_at DESC, o.order_id DESC
) AS last3
WHERE c.customer_id IN (1, 3, 37)
ORDER BY c.customer_id, last3.ordered_at DESC;

customer_id  first_name  order_id  ordered_at
-----------  ----------  --------  -------------------
1            Ada         369       2026-06-10 21:27:57
1            Ada         357       2026-05-14 05:50:22
1            Ada         356       2026-05-06 13:11:46
3            Grace       377       2026-06-20 16:58:05
3            Grace       375       2026-06-19 17:38:41
3            Grace       371       2026-06-14 10:23:52

(6 rows affected)

The subquery is evaluated once per customer and sees c.customer_id. The tie-breaker rule from post 5 applies here as well: since two orders can share the same second, we added order_id DESC. Customer 37, Mert, is not in the result because he has no orders and CROSS APPLY behaves like an INNER JOIN. OUTER APPLY behaves like a LEFT JOIN and brings Mert back as a seventh row with NULLs.

The standard form of the same query is LEFT JOIN LATERAL (...) AS last3 ON true. PostgreSQL 18.6 with FETCH FIRST 3 ROWS ONLY in the subquery and MySQL 8.4 with LIMIT 3 return the same seven rows (Mert with NULLs). SQL Server does not recognise LATERAL and fails with Incorrect syntax near the keyword 'SELECT'; in T-SQL this job is done only with APPLY. APPLY has one more use: computing an expression once and using it by name in both SELECT and WHERE. The price_with_tax alias we could not use in WHERE in post 5 becomes usable as WHERE x.price_with_tax > 1500 with CROSS APPLY (SELECT b.list_price * 1.10 AS price_with_tax) AS x, and the query returns Design Patterns and The Art of Computer Programming. The other solution for top-N per group is ROW_NUMBER(); we compare the two in post 13.

Fan-Out: Joining Two One-to-Many Relationships at Once

Back to the opening query. Let's look row by row at what happens when we join the order's items and payments at the same time:

SELECT oi.book_id, oi.quantity * oi.unit_price AS line_total, p.method, p.amount
FROM dbo.order_items AS oi
JOIN dbo.payments AS p ON p.order_id = oi.order_id
WHERE oi.order_id = 372
ORDER BY oi.book_id, p.method;

book_id  line_total  method     amount
-------  ----------  ---------  ------
43       300.50      card       931.50
43       300.50      gift_card  200.00
48       430.00      card       931.50
48       430.00      gift_card  200.00
53       401.00      card       931.50
53       401.00      gift_card  200.00

(6 rows affected)

Three items × two payments = six rows. There is no direct relationship between items and payments; both belong to the order, and the join matched every item with every payment. Over these six rows SUM(line_total) counts every item twice and SUM(amount) counts every payment three times. This fan-out (or "chasm trap") raises no error; the result is a plausible-looking but wrong number. Across all orders, the same join shows an item total of 450,586.11 TRY and a payment total of 821,113.71 TRY; the correct totals are both 361,707.08 TRY. The payment side of the report is inflated more than twofold.

The fix is to bring each one-to-many side down to the order grain before the join, that is, to group first and join afterwards:

SELECT o.order_id, i.items_total, p.paid_total
FROM dbo.orders AS o
JOIN (SELECT order_id, SUM(quantity * unit_price) AS items_total
      FROM dbo.order_items
      GROUP BY order_id) AS i ON i.order_id = o.order_id
JOIN (SELECT order_id, SUM(amount) AS paid_total
      FROM dbo.payments
      GROUP BY order_id) AS p ON p.order_id = o.order_id
WHERE o.order_id = 372;

order_id  items_total  paid_total
--------  -----------  ----------
372       1131.50      1131.50

(1 rows affected)

Each derived table produces one row per order; the join is now one-to-one and the totals are right. You can do the same with correlated subqueries or CROSS APPLY. The rule: if you join two different child tables of the same parent in one query and aggregate both, bring at least one of them down to the parent grain before the join. That was also the reason for COUNT(DISTINCT ...) in the review rate query in post 6; DISTINCT can be a patch for COUNT but not for SUM, because two different items can happen to have the same amount.

In Production

Fan-out shows up most often in reports and dashboards and goes unnoticed on test data: since most test orders have a single payment, the totals match, and the numbers inflate on the day split payments start in real data. Writing the grain of the result (what does one row represent?) in a comment in every report query catches this bug early. On the ORM (Object-Relational Mapping) side, the same trap appears in Entity Framework Core (EF Core) when you Include several collections in one query and is known as "cartesian explosion"; the split query in the EF Core series post "EF Core'da İlişkili Veri Yükleme: Include, Explicit ve Lazy Loading, Split Query" (loading related data) was the fix. APPLY and correlated subqueries may run once per row of the outer query; if the outer side is large and there is no suitable index on the inner side, the cost grows quickly. An index such as orders (customer_id, ordered_at) reduces the "last three orders per customer" query to a read of a few pages per customer; index design is the topic of the "SQL Server Derinlemesine" (SQL Server in Depth) series.

Common Pitfalls

• Joining two child tables of the same parent and taking SUM of both; the totals get multiplied.
• Writing a semi join as JOIN + DISTINCT; unnecessary rows are produced first and thrown away later.
• Writing an anti join with NOT IN on a nullable column.
• Using INNER JOIN in a self join and losing the root rows (those without a parent category).
• Using BETWEEN in range joins and dropping boundary values into two groups.
• Trying to reference an outer query column from a derived table; that requires APPLY or LATERAL.
• Using CROSS APPLY and losing rows without a match; use OUTER APPLY to keep them.

A Small Experiment

★☆☆ List every author's name and the number of books they wrote; the two authors without books should show 0. Books with several authors (such as Design Patterns) must count separately for each author.

★★☆ List the two most expensive books of each category with CROSS APPLY. Switch the query to OUTER APPLY so that categories without books show up too, and explain the difference in row counts.

★★★ For every customer, compute the number of orders, the total item amount and the total payment amount in one query. First write the fan-out version on purpose and find the customers whose numbers are inflated; then fix it with pre-aggregation and verify that the two totals are equal for paid orders.

In this post we touched EXISTS, NOT EXISTS and NOT IN with subqueries and fixed fan-out with GROUP BY. In the next post of the series we move on to aggregates themselves: GROUP BY, HAVING, the three forms of COUNT, conditional aggregation with SUM(CASE ...) and subtotals with ROLLUP.