Skip to main content

SQL FULL OUTER JOIN and CROSS JOIN

You have now mastered INNER JOIN and LEFT JOIN, which together handle the vast majority of real-world SQL queries. But SQL offers two more join types that complete the picture: FULL OUTER JOIN and CROSS JOIN. These are specialized tools that you will use less frequently, but when you need them, nothing else will do.

FULL OUTER JOIN returns all rows from both tables, matching where it can and filling in NULLs where it cannot. It reveals unmatched data on both sides simultaneously. CROSS JOIN takes a completely different approach: it pairs every row from one table with every row from another, producing a cartesian product. No matching condition, no filtering, just every possible combination.

This guide explains both join types, shows you exactly when they are useful (and when they are dangerous), walks through practical examples, and clarifies why most developers rarely need them compared to INNER JOIN and LEFT JOIN. Every example uses the ShopSmart sample database with full outputs (we defined it in a previous guide here, and then we extended it here).

FULL OUTER JOIN Explained​

A FULL OUTER JOIN (also written as FULL JOIN) combines the behavior of LEFT JOIN and RIGHT JOIN. It returns:

  • All rows from the left table, with NULLs for unmatched right columns
  • All rows from the right table, with NULLs for unmatched left columns
  • All matched rows from both tables combined
SELECT columns
FROM table_a
FULL OUTER JOIN table_b ON table_a.column = table_b.column;

In other words, no row is ever excluded. Every row from both tables appears in the results, regardless of whether it has a match.

Visualizing FULL OUTER JOIN​

    ┌───────────────┐     ┌───────────────┐
    │               │     │               │
    │   table_a     │     │   table_b     │
    │            ┌──┼─────┼──┐            │
    │ unmatched  │  │     │  │ unmatched  │
    │  left rows │ matched│  │ right rows │
    │            │  rows  │  │            │
    │  INCLUDED  │ INCLUDED  │  INCLUDED  │
    │  (NULLs on │        │  │ (NULLs on  │
    │   right)   └──┼─────┼──┘  left)     │
    │               │     │               │
    └───────────────┘     └───────────────┘
    
    ◄────── FULL OUTER JOIN returns everything ──────►

Compare this with the other join types:

Join TypeLeft unmatchedMatched rowsRight unmatched
INNER JOINExcludedIncludedExcluded
LEFT JOINIncluded (NULLs on right)IncludedExcluded
RIGHT JOINExcludedIncludedIncluded (NULLs on left)
FULL OUTER JOINIncluded (NULLs on right)IncludedIncluded (NULLs on left)

Your First FULL OUTER JOIN​

To demonstrate FULL OUTER JOIN properly, we need data with unmatched rows on both sides. Let us create a scenario using temporary tables:

-- Create a table of product promotions (some products don't have promotions)
CREATE TABLE promotions (
    id INTEGER PRIMARY KEY,
    product_id INTEGER,
    discount_pct DECIMAL(5, 2),
    promo_name VARCHAR(100),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

INSERT INTO promotions VALUES (1, 1, 15.00, 'Mouse Madness');
INSERT INTO promotions VALUES (2, 2, 10.00, 'Keyboard Special');
INSERT INTO promotions VALUES (3, 6, 20.00, 'Kitchen Sale');
INSERT INTO promotions VALUES (4, 11, 25.00, 'Mystery Product Deal');
-- product_id 11 doesn't exist in our products table (for demonstration)
Note on the Example

We intentionally inserted a promotion referencing product_id = 11, which does not exist in the products table. In a real database with foreign key constraints, this insert would fail. We are simulating a scenario where the constraint is missing or the data comes from an external source.

Now let us see what FULL OUTER JOIN reveals:

SELECT p.id AS product_id,
       p.name AS product_name,
       pr.id AS promo_id,
       pr.promo_name,
       pr.discount_pct
FROM products p
FULL OUTER JOIN promotions pr ON p.id = pr.product_id
ORDER BY p.id;

Output:

product_idproduct_namepromo_idpromo_namediscount_pct
1Wireless Mouse1Mouse Madness15.00
2Mechanical Keyboard2Keyboard Special10.00
3USB-C HubNULLNULLNULL
4SQL for BeginnersNULLNULLNULL
5Data Science HandbookNULLNULLNULL
6Coffee Maker Pro3Kitchen Sale20.00
7Yoga Mat PremiumNULLNULLNULL
8Running Shoes X1NULLNULLNULL
9Bluetooth SpeakerNULLNULLNULL
10Stainless Water BottleNULLNULLNULL
NULLNULL4Mystery Product Deal25.00

Three distinct groups are visible in the results:

  1. Matched rows (products 1, 2, 6): Both product and promotion data are present
  2. Left-only rows (products 3, 4, 5, 7, 8, 9, 10): Products without promotions, promotion columns are NULL
  3. Right-only row (promo 4): A promotion referencing a non-existent product, product columns are NULL

This is the unique power of FULL OUTER JOIN: it shows you everything, including mismatches on both sides.

Understanding Each Result Group​

-- See only the matched rows (same as INNER JOIN)
SELECT p.name, pr.promo_name
FROM products p
FULL OUTER JOIN promotions pr ON p.id = pr.product_id
WHERE p.id IS NOT NULL AND pr.id IS NOT NULL;

Output:

namepromo_name
Wireless MouseMouse Madness
Mechanical KeyboardKeyboard Special
Coffee Maker ProKitchen Sale
-- See only products WITHOUT promotions (left-only)
SELECT p.name AS product_without_promotion
FROM products p
FULL OUTER JOIN promotions pr ON p.id = pr.product_id
WHERE pr.id IS NULL;

Output:

product_without_promotion
USB-C Hub
SQL for Beginners
Data Science Handbook
Yoga Mat Premium
Running Shoes X1
Bluetooth Speaker
Stainless Water Bottle
-- See only promotions WITHOUT valid products (right-only, orphaned data)
SELECT pr.promo_name AS orphaned_promotion,
       pr.product_id AS invalid_product_id
FROM products p
FULL OUTER JOIN promotions pr ON p.id = pr.product_id
WHERE p.id IS NULL;

Output:

orphaned_promotioninvalid_product_id
Mystery Product Deal11

When to Use FULL OUTER JOIN​

FULL OUTER JOIN is a specialized tool. Here are the scenarios where it genuinely shines.

Data Reconciliation​

When comparing two datasets that should match but might have discrepancies on either side:

-- Compare expected inventory (from a spreadsheet import) with actual products
-- Find items in one list but not the other

CREATE TABLE expected_products (
    sku VARCHAR(20) PRIMARY KEY,
    name VARCHAR(100),
    expected_stock INTEGER
);

INSERT INTO expected_products VALUES ('WM-001', 'Wireless Mouse', 150);
INSERT INTO expected_products VALUES ('MK-001', 'Mechanical Keyboard', 75);
INSERT INTO expected_products VALUES ('HP-001', 'Headphones Pro', 40);

CREATE TABLE actual_inventory (
    sku VARCHAR(20) PRIMARY KEY,
    name VARCHAR(100),
    actual_stock INTEGER
);

INSERT INTO actual_inventory VALUES ('WM-001', 'Wireless Mouse', 148);
INSERT INTO actual_inventory VALUES ('MK-001', 'Mechanical Keyboard', 75);
INSERT INTO actual_inventory VALUES ('UC-001', 'USB-C Hub', 200);
SELECT COALESCE(e.sku, a.sku) AS sku,
       COALESCE(e.name, a.name) AS product,
       e.expected_stock,
       a.actual_stock,
       CASE
           WHEN e.sku IS NULL THEN 'In inventory but not expected'
           WHEN a.sku IS NULL THEN 'Expected but not in inventory'
           WHEN e.expected_stock = a.actual_stock THEN 'Match'
           ELSE 'Stock mismatch'
       END AS status
FROM expected_products e
FULL OUTER JOIN actual_inventory a ON e.sku = a.sku
ORDER BY sku;

Output:

skuproductexpected_stockactual_stockstatus
HP-001Headphones Pro40NULLExpected but not in inventory
MK-001Mechanical Keyboard7575Match
UC-001USB-C HubNULL200In inventory but not expected
WM-001Wireless Mouse150148Stock mismatch

This immediately shows four types of situations: matches, mismatches, items missing from inventory, and unexpected items in inventory. No other join type can reveal all four in a single query.

Finding Orphaned Records​

When data integrity might be compromised (perhaps foreign keys were not enforced or data was imported from external sources):

-- Find products without categories AND categories without products
SELECT COALESCE(cat.name, '[No Category]') AS category,
       COALESCE(p.name, '[No Product]') AS product,
       CASE
           WHEN cat.id IS NULL THEN 'Product missing category'
           WHEN p.id IS NULL THEN 'Empty category'
           ELSE 'OK'
       END AS status
FROM categories cat
FULL OUTER JOIN products p ON cat.id = p.category_id
ORDER BY category, product;

Output:

categoryproductstatus
BooksData Science HandbookOK
BooksSQL for BeginnersOK
ElectronicsBluetooth SpeakerOK
ElectronicsMechanical KeyboardOK
ElectronicsUSB-C HubOK
ElectronicsWireless MouseOK
Home & KitchenCoffee Maker ProOK
SportsRunning Shoes X1OK
SportsStainless Water BottleOK
SportsYoga Mat PremiumOK
Toys[No Product]Empty category

The Toys category has no products. In a well-maintained database, this might be intentional. In a data migration scenario, it might indicate missing data.

Comparing Two Versions of Data​

When you need to detect what changed between two snapshots:

CREATE TABLE prices_last_month (
    product_id INTEGER,
    price DECIMAL(10, 2)
);

CREATE TABLE prices_this_month (
    product_id INTEGER,
    price DECIMAL(10, 2)
);

INSERT INTO prices_last_month VALUES (1, 29.99), (2, 84.99), (3, 45.00), (5, 42.50);
INSERT INTO prices_this_month VALUES (1, 29.99), (2, 89.99), (3, 45.00), (4, 34.99);
SELECT COALESCE(lm.product_id, tm.product_id) AS product_id,
       lm.price AS old_price,
       tm.price AS new_price,
       CASE
           WHEN lm.product_id IS NULL THEN 'New product'
           WHEN tm.product_id IS NULL THEN 'Removed product'
           WHEN lm.price = tm.price THEN 'No change'
           WHEN tm.price > lm.price THEN 'Price increased'
           ELSE 'Price decreased'
       END AS change_type
FROM prices_last_month lm
FULL OUTER JOIN prices_this_month tm ON lm.product_id = tm.product_id
ORDER BY product_id;

Output:

product_idold_pricenew_pricechange_type
129.9929.99No change
284.9989.99Price increased
345.0045.00No change
4NULL34.99New product
542.50NULLRemoved product

This is a classic change-detection query that only FULL OUTER JOIN can handle cleanly.

FULL OUTER JOIN in MySQL: The Workaround​

MySQL does not support FULL OUTER JOIN syntax. If you need this functionality in MySQL, you can simulate it by combining a LEFT JOIN and a RIGHT JOIN with UNION:

-- MySQL workaround for FULL OUTER JOIN
SELECT p.id AS product_id, p.name, pr.promo_name
FROM products p
LEFT JOIN promotions pr ON p.id = pr.product_id

UNION

SELECT p.id AS product_id, p.name, pr.promo_name
FROM products p
RIGHT JOIN promotions pr ON p.id = pr.product_id;

UNION combines both result sets and removes duplicates. The LEFT JOIN captures all left-table rows (including unmatched). The RIGHT JOIN captures all right-table rows (including unmatched). Together, they cover everything a FULL OUTER JOIN would return.

UNION vs UNION ALL

Use UNION (not UNION ALL) for this workaround. UNION removes duplicate rows that appear in both the LEFT JOIN and RIGHT JOIN results (the matched rows). UNION ALL would include those duplicates, giving you incorrect results.

CROSS JOIN Explained​

CROSS JOIN is fundamentally different from every other join type. It does not match rows based on a condition. Instead, it produces the cartesian product: every row from the first table paired with every row from the second table.

SELECT columns
FROM table_a
CROSS JOIN table_b;

If table A has 5 rows and table B has 4 rows, the result has 5 x 4 = 20 rows, one for every possible combination.

There is no ON clause because there is no matching condition. Every row pairs with every other row unconditionally.

A Simple CROSS JOIN Example​

SELECT c.name AS category, s.label AS status
FROM categories c
CROSS JOIN (
    SELECT 'In Stock' AS label
    UNION ALL SELECT 'Low Stock'
    UNION ALL SELECT 'Out of Stock'
) s
ORDER BY c.name, s.label;

Output:

categorystatus
BooksIn Stock
BooksLow Stock
BooksOut of Stock
ElectronicsIn Stock
ElectronicsLow Stock
ElectronicsOut of Stock
Home & KitchenIn Stock
Home & KitchenLow Stock
Home & KitchenOut of Stock
SportsIn Stock
SportsLow Stock
SportsOut of Stock
ToysIn Stock
ToysLow Stock
ToysOut of Stock

5 categories x 3 statuses = 15 rows. Every category is paired with every status.

Visualizing CROSS JOIN​

Category Table          Status Values         CROSS JOIN Result
┌────────────────┐     ┌──────────────┐      ┌────────────────┬──────────────┐
│ Books          │     │ In Stock     │      │ Books          │ In Stock     │
│ Electronics    │  ×  │ Low Stock    │  =   │ Books          │ Low Stock    │
│ Home & Kitchen │     │ Out of Stock │      │ Books          │ Out of Stock │
│ Sports         │     └──────────────┘      │ Electronics    │ In Stock     │
│ Toys           │                           │ Electronics    │ Low Stock    │
└────────────────┘                           │ Electronics    │ Out of Stock │
                                             │ ... (9 more)   │              │
    5 rows          ×     3 rows         =   │                │              │
                                             └────────────────┴──────────────┘
                                                    15 rows

The Cartesian Product Danger​

CROSS JOIN can produce enormous result sets with even modest tables:

Table A RowsTable B RowsResult Rows
1010100
10010010,000
1,0001,0001,000,000
10,00010,000100,000,000
CROSS JOIN Can Crash Your Database

Never run a CROSS JOIN between two large tables without understanding the result size. A CROSS JOIN between two tables with 10,000 rows each produces 100 million rows. This can consume all available memory, fill up disk space, and bring your database server to a halt.

-- DANGEROUS: If products has 10,000 rows and orders has 50,000 rows
-- This produces 500,000,000 rows
SELECT *
FROM products
CROSS JOIN orders;
-- DON'T DO THIS

Always calculate the expected result size (rows in A × rows in B) before running a CROSS JOIN.

Practical CROSS JOIN Use Cases​

Despite its dangers, CROSS JOIN has legitimate and elegant use cases.

Generating a Report Framework​

When you need a complete grid that includes every possible combination, even those with no data:

-- Create a grid of all categories × all months
-- Then LEFT JOIN actual data to find gaps

SELECT grid.category,
       grid.month,
       COALESCE(actual.order_count, 0) AS orders,
       COALESCE(actual.revenue, 0) AS revenue
FROM (
    -- Generate the complete grid
    SELECT cat.name AS category, m.month
    FROM categories cat
    CROSS JOIN (
        SELECT '2024-01' AS month
        UNION ALL SELECT '2024-02'
        UNION ALL SELECT '2024-03'
        UNION ALL SELECT '2024-04'
    ) m
) grid
LEFT JOIN (
    -- Calculate actual data
    SELECT cat.name AS category,
           TO_CHAR(o.order_date, 'YYYY-MM') AS month,
           COUNT(DISTINCT o.id) AS order_count,
           ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue
    FROM categories cat
    JOIN products p ON cat.id = p.category_id
    JOIN order_items oi ON p.id = oi.product_id
    JOIN orders o ON oi.order_id = o.id
    GROUP BY cat.name, TO_CHAR(o.order_date, 'YYYY-MM')
) actual ON grid.category = actual.category AND grid.month = actual.month
ORDER BY grid.category, grid.month;

Output:

categorymonthordersrevenue
Books2024-0100
Books2024-02134.99
Books2024-03134.99
Books2024-0400
Electronics2024-012194.97
Electronics2024-0200
Electronics2024-03145.00
Electronics2024-044314.96
Home & Kitchen2024-0100
Home & Kitchen2024-0200
Home & Kitchen2024-031129.99
Home & Kitchen2024-0400
Sports2024-0100
Sports2024-02124.99
Sports2024-031110.00
Sports2024-044345.98
Toys2024-0100
Toys2024-0200
Toys2024-0300
Toys2024-0400

Without the CROSS JOIN, months with zero activity would be missing from the results entirely. The CROSS JOIN generates every category-month combination (5 categories x 4 months = 20 rows), and the LEFT JOIN fills in actual data where it exists. This creates a complete, gap-free report perfect for charts and dashboards.

This Is the Most Common CROSS JOIN Pattern

The pattern of CROSS JOIN to generate a framework + LEFT JOIN to fill in data is by far the most practical use of CROSS JOIN. It ensures your reports and charts have no missing rows, even when there is no data for certain combinations.

Size and Color Combinations for a Product​

CREATE TABLE sizes (
    id INTEGER PRIMARY KEY,
    label VARCHAR(10)
);

CREATE TABLE colors (
    id INTEGER PRIMARY KEY,
    name VARCHAR(20)
);

INSERT INTO sizes VALUES (1, 'S'), (2, 'M'), (3, 'L'), (4, 'XL');
INSERT INTO colors VALUES (1, 'Red'), (2, 'Blue'), (3, 'Black');
-- Generate all possible size-color variants for a product
SELECT s.label AS size,
       c.name AS color,
       'Running Shoes X1 - ' || s.label || ' ' || c.name AS variant_name
FROM sizes s
CROSS JOIN colors c
ORDER BY s.id, c.id;

Output:

sizecolorvariant_name
SRedRunning Shoes X1 - S Red
SBlueRunning Shoes X1 - S Blue
SBlackRunning Shoes X1 - S Black
MRedRunning Shoes X1 - M Red
MBlueRunning Shoes X1 - M Blue
MBlackRunning Shoes X1 - M Black
LRedRunning Shoes X1 - L Red
LBlueRunning Shoes X1 - L Blue
LBlackRunning Shoes X1 - L Black
XLRedRunning Shoes X1 - XL Red
XLBlueRunning Shoes X1 - XL Blue
XLBlackRunning Shoes X1 - XL Black

4 sizes x 3 colors = 12 variants. This is a clean, legitimate use of CROSS JOIN for generating combinatorial data.

Date Series Generation​

Generating a series of dates for reporting frameworks (PostgreSQL-specific for generate_series, but the concept applies everywhere):

-- PostgreSQL: Generate a date for every day in January 2024
SELECT d::date AS report_date,
       COALESCE(order_data.order_count, 0) AS orders,
       COALESCE(order_data.revenue, 0) AS revenue
FROM generate_series('2024-01-01'::date, '2024-01-31'::date, '1 day') d
LEFT JOIN (
    SELECT order_date,
           COUNT(*) AS order_count,
           SUM(total_amount) AS revenue
    FROM orders
    GROUP BY order_date
) order_data ON d::date = order_data.order_date
WHERE COALESCE(order_data.order_count, 0) > 0
   OR EXTRACT(DOW FROM d) BETWEEN 1 AND 5  -- Weekdays only
ORDER BY d
LIMIT 15;

While this uses generate_series instead of CROSS JOIN, the concept is identical: create a complete framework of dates, then LEFT JOIN actual data to fill in the values.

Comparing Every Product Pair​

For recommendation engines or similarity analysis:

-- Find all pairs of products in the same category
SELECT p1.name AS product_a,
       p2.name AS product_b,
       p1.price AS price_a,
       p2.price AS price_b,
       ABS(p1.price - p2.price) AS price_difference
FROM products p1
CROSS JOIN products p2
WHERE p1.category_id = p2.category_id
  AND p1.id < p2.id  -- Avoid duplicates and self-pairs
ORDER BY price_difference ASC
LIMIT 10;

Output:

product_aproduct_bprice_aprice_bprice_difference
SQL for BeginnersData Science Handbook34.9942.57.509999999999998
Yoga Mat PremiumStainless Water Bottle3824.9913.010000000000002
Wireless MouseUSB-C Hub29.994515.010000000000002
USB-C HubBluetooth Speaker456520
Mechanical KeyboardBluetooth Speaker89.996524.989999999999995
Wireless MouseBluetooth Speaker29.996535.010000000000005
Mechanical KeyboardUSB-C Hub89.994544.989999999999995
Wireless MouseMechanical Keyboard29.9989.9960
Yoga Mat PremiumRunning Shoes X13811072
Running Shoes X1Stainless Water Bottle11024.9985.01

The WHERE p1.id < p2.id condition is critical. Without it, you would get both "Mouse vs Keyboard" and "Keyboard vs Mouse" (duplicates), plus "Mouse vs Mouse" (self-pairs).

Accidental CROSS JOINs​

One of the most important things to understand about CROSS JOIN is that you can create one accidentally by forgetting the ON clause in an INNER JOIN or by using the older comma-separated table syntax without a WHERE condition:

-- Accidental CROSS JOIN: Forgot the ON clause (some databases allow this)
SELECT c.first_name, p.name
FROM customers c, products p;
-- 7 customers × 10 products = 70 rows of nonsense!
-- Accidental CROSS JOIN: Implicit join syntax with missing WHERE
SELECT c.first_name, o.order_date
FROM customers c, orders o;
-- 7 customers × 12 orders = 84 rows of nonsense!
Common Mistake: The Implicit Cross Join

The old-style comma-separated FROM syntax (FROM table_a, table_b) is equivalent to a CROSS JOIN when no WHERE clause connects the tables. This is the most common accidental cross join.

-- Implicit CROSS JOIN (dangerous old syntax)
SELECT c.first_name, o.order_date
FROM customers c, orders o;
-- Returns 84 rows (7 × 12), most of which are meaningless

-- Explicit INNER JOIN (correct modern syntax)
SELECT c.first_name, o.order_date
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;
-- Returns 12 rows, each correctly matched

Always use explicit JOIN syntax with an ON clause. Never use comma-separated tables unless you intentionally want a CROSS JOIN, and even then, use the explicit CROSS JOIN keyword for clarity.

Alternative CROSS JOIN Syntax​

There are multiple ways to write a CROSS JOIN, all producing identical results:

-- Explicit CROSS JOIN keyword (recommended)
SELECT s.label, c.name
FROM sizes s
CROSS JOIN colors c;

-- Comma-separated tables (implicit cross join, older syntax)
SELECT s.label, c.name
FROM sizes s, colors c;

-- INNER JOIN with always-true condition (unusual but works)
SELECT s.label, c.name
FROM sizes s
INNER JOIN colors c ON 1 = 1;

The first form is the clearest and most maintainable. It makes your intent explicit: you deliberately want every combination.

Complete Join Type Reference​

Here is a comprehensive comparison of all join types you have learned:

-- Setup for comparison: customers (7 rows) and orders (12 rows)
-- Grace Taylor has no orders

-- INNER JOIN: Only matched rows
SELECT c.first_name, o.id AS order_id
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;
-- Result: 12 rows (Grace excluded)

-- LEFT JOIN: All left rows + matched right rows
SELECT c.first_name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;
-- Result: 13 rows (Grace included with NULL order)

-- RIGHT JOIN: Matched left rows + all right rows
SELECT c.first_name, o.id AS order_id
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;
-- Result: 12 rows (same as INNER here because FK ensures every order has a customer)

-- FULL OUTER JOIN: All rows from both tables
SELECT c.first_name, o.id AS order_id
FROM customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id;
-- Result: 13 rows (Grace included, no orphaned orders in this case)

-- CROSS JOIN: Every combination
SELECT c.first_name, o.id AS order_id
FROM customers c
CROSS JOIN orders o;
-- Result: 84 rows (7 × 12)

Choosing the Right Join Type​

Do you need EVERY possible combination?
├── Yes → CROSS JOIN (use with extreme caution)
└── No
    ├── Do you need unmatched rows from BOTH tables?
    │   ├── Yes → FULL OUTER JOIN
    │   └── No
    │       ├── Do you need ALL rows from the primary table?
    │       │   ├── Yes → LEFT JOIN (put primary table on left)
    │       │   └── No → INNER JOIN
    │       └── (RIGHT JOIN: Just use LEFT JOIN with swapped tables)

Practical Exercises​

Exercise 1​

Using FULL OUTER JOIN, write a query that shows all products and all promotions, indicating which products have promotions and which promotions reference valid products.

SELECT COALESCE(p.name, '[Unknown Product]') AS product,
       COALESCE(pr.promo_name, '[No Promotion]') AS promotion,
       pr.discount_pct,
       CASE
           WHEN p.id IS NOT NULL AND pr.id IS NOT NULL THEN 'Active Promotion'
           WHEN p.id IS NOT NULL AND pr.id IS NULL THEN 'No Promotion'
           ELSE 'Orphaned Promotion'
       END AS status
FROM products p
FULL OUTER JOIN promotions pr ON p.id = pr.product_id
ORDER BY status, product;
productpromotiondiscout_pctstatus
Coffee Maker ProKitchen Sale20Active Promotion
Mechanical KeyboardKeyboard Special10Active Promotion
Wireless MouseMouse Madness15Active Promotion
Bluetooth Speaker[No Promotion]nullNo Promotion
Data Science Handbook[No Promotion]nullNo Promotion
Running Shoes X1[No Promotion]nullNo Promotion
SQL for Beginners[No Promotion]nullNo Promotion
Stainless Water Bottle[No Promotion]nullNo Promotion
USB-C Hub[No Promotion]nullNo Promotion
Yoga Mat Premium[No Promotion]nullNo Promotion
[Unknown Product]Mystery Product Deal25Orphaned Promotion

Exercise 2​

Use CROSS JOIN to generate a grid of all categories and all order statuses, then show how many orders exist for each combination.

SELECT grid.category,
       grid.status,
       COALESCE(actual.order_count, 0) AS order_count
FROM (
    SELECT cat.name AS category, s.status
    FROM categories cat
    CROSS JOIN (
        SELECT DISTINCT status FROM orders
    ) s
) grid
LEFT JOIN (
    SELECT cat.name AS category,
           o.status,
           COUNT(*) AS order_count
    FROM orders o
    JOIN order_items oi ON o.id = oi.order_id
    JOIN products p ON oi.product_id = p.id
    JOIN categories cat ON p.category_id = cat.id
    GROUP BY cat.name, o.status
) actual ON grid.category = actual.category AND grid.status = actual.status
ORDER BY grid.category, grid.status;
categorystatusorder_count
Bookscompleted1
Bookspending1
Booksshipped0
Electronicscompleted4
Electronicspending2
Electronicsshipped2
Home & Kitchencompleted0
Home & Kitchenpending0
Home & Kitchenshipped1
Sportscompleted3
Sportspending3
Sportsshipped2
Toyscompleted0
Toyspending0
Toysshipped0

Exercise 3​

Find all customers who have no orders AND all orders that somehow have no valid customer (if any exist), using FULL OUTER JOIN.

SELECT c.first_name || ' ' || c.last_name AS customer,
       o.id AS order_id,
       CASE
           WHEN c.id IS NULL THEN 'Orphaned order'
           WHEN o.id IS NULL THEN 'Customer without orders'
           ELSE 'Matched'
       END AS status
FROM customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id
WHERE c.id IS NULL OR o.id IS NULL;

Expected output:

customerorder_idstatus
Grace TaylorNULLCustomer without orders

Exercise 4​

Use CROSS JOIN to create all possible product pairs within the same price tier (Under $50, $50-$100, Over $100), excluding self-pairs.

SELECT p1.name AS product_a,
       p2.name AS product_b,
       CASE
           WHEN p1.price < 50 THEN 'Under $50'
           WHEN p1.price < 100 THEN '$50-$100'
           ELSE 'Over $100'
       END AS price_tier
FROM products p1
CROSS JOIN products p2
WHERE p1.id < p2.id
  AND (
    CASE WHEN p1.price < 50 THEN 1 WHEN p1.price < 100 THEN 2 ELSE 3 END
    =
    CASE WHEN p2.price < 50 THEN 1 WHEN p2.price < 100 THEN 2 ELSE 3 END
  )
ORDER BY price_tier, product_a;
categorystatusorder_count
Bookscompleted1
Bookspending1
Booksshipped0
Electronicscompleted4
Electronicspending2
Electronicsshipped2
Home & Kitchencompleted0
Home & Kitchenpending0
Home & Kitchenshipped1
Sportscompleted3
Sportspending3
Sportsshipped2
Toyscompleted0
Toyspending0
Toysshipped0

Exercise 5​

Create a monthly product review summary that includes months with zero reviews, using CROSS JOIN to generate the framework.

SELECT grid.product,
       grid.month,
       COALESCE(actual.review_count, 0) AS reviews,
       COALESCE(actual.avg_rating, 0) AS avg_rating
FROM (
    SELECT p.name AS product, p.id AS product_id, m.month
    FROM products p
    CROSS JOIN (
        SELECT '2024-01' AS month
        UNION ALL SELECT '2024-02'
        UNION ALL SELECT '2024-03'
        UNION ALL SELECT '2024-04'
    ) m
    WHERE p.id IN (1, 2, 8)
) grid
LEFT JOIN (
    SELECT product_id,
           TO_CHAR(review_date, 'YYYY-MM') AS month,
           COUNT(*) AS review_count,
           ROUND(AVG(rating), 1) AS avg_rating
    FROM reviews
    GROUP BY product_id, TO_CHAR(review_date, 'YYYY-MM')
) actual ON grid.product_id = actual.product_id AND grid.month = actual.month
ORDER BY grid.product, grid.month;
productmonthreviewsavg_rating
Mechanical Keyboard2024-0115
Mechanical Keyboard2024-0214
Mechanical Keyboard2024-0313
Mechanical Keyboard2024-0400
Running Shoes X12024-0100
Running Shoes X12024-0200
Running Shoes X12024-0324.5
Running Shoes X12024-0415
Wireless Mouse2024-0124.5
Wireless Mouse2024-0215
Wireless Mouse2024-0300
Wireless Mouse2024-0400

Cleanup​

Remove the temporary tables we created for the examples:

DROP TABLE IF EXISTS promotions;
DROP TABLE IF EXISTS sizes;
DROP TABLE IF EXISTS colors;
DROP TABLE IF EXISTS expected_products;
DROP TABLE IF EXISTS actual_inventory;
DROP TABLE IF EXISTS prices_last_month;
DROP TABLE IF EXISTS prices_this_month;

Key Takeaways​

FULL OUTER JOIN and CROSS JOIN are specialized tools that complete your understanding of SQL joins. Here is what you should remember:

  • FULL OUTER JOIN returns all rows from both tables, filling NULLs where no match exists. No row from either table is ever excluded.
  • Use FULL OUTER JOIN for data reconciliation, orphan detection, and comparing two datasets that might have mismatches on either side
  • MySQL does not support FULL OUTER JOIN. Simulate it with a LEFT JOIN UNION RIGHT JOIN
  • CROSS JOIN produces a cartesian product: every row from table A paired with every row from table B. No ON clause is used.
  • Result size of CROSS JOIN = rows in A × rows in B. Always calculate this before running.
  • The most practical CROSS JOIN pattern is generating a report framework (all combinations of dimensions), then LEFT JOINing actual data onto it
  • CROSS JOIN is useful for generating size/color combinations, date grids, product pairs, and other combinatorial data
  • Accidental cross joins happen when you use comma-separated tables without a WHERE clause. Always use explicit JOIN syntax.
  • In daily work, INNER JOIN and LEFT JOIN handle 95%+ of all join needs. Use FULL OUTER JOIN and CROSS JOIN only when their specific behavior is required.
  • The complete join hierarchy: INNER (only matches) → LEFT/RIGHT (all from one side) → FULL OUTER (all from both sides) → CROSS (all combinations)

Understanding all four join types gives you the complete picture of how SQL combines data from multiple tables. For most queries, INNER JOIN and LEFT JOIN are your workhorses. For data quality checks and reconciliation, FULL OUTER JOIN is invaluable. For generating frameworks and combinations, CROSS JOIN is the right tool. Knowing when to reach for each one is what separates a competent SQL developer from a proficient one.

Table of Contents