SQL FULL OUTER JOIN and CROSS JOIN

FULL OUTER JOIN keeps every row from both tables, matching where it can and filling NULLs where it can't. CROSS JOIN pairs every row of one table with every row of another. Both are less common than INNER and LEFT joins, but each solves problems the others can't.

Setup for this lesson

ShopKart's tables always match (every order has a customer), so to see FULL OUTER JOIN in action we need a table that only partly overlaps. Run this first to create a small table of city sales targets:

CREATE TABLE city_targets (
  city            VARCHAR(50) PRIMARY KEY,
  target_customers INT
);

INSERT INTO city_targets VALUES
('Bengaluru', 3), ('Mumbai', 2), ('Delhi', 2), ('Indore', 1), ('Surat', 1);

Result:

Query OK, 5 row(s) affected.

Bengaluru, Mumbai and Delhi have customers; Indore and Surat don't. Many customer cities (Kochi, Pune…) have no target.

FULL OUTER JOIN

Syntax

SELECT ...
FROM table_a a
FULL OUTER JOIN table_b b ON a.key = b.key;

1. Customers and targets, matched or not

SELECT c.customer_name,
       c.city AS customer_city,
       t.city AS target_city,
       t.target_customers
FROM customers c
FULL OUTER JOIN city_targets t ON t.city = c.city
ORDER BY t.city NULLS FIRST, c.customer_name
LIMIT 10;

Result:

(10 rows)

You see three kinds of rows: customers with a target (both sides filled), customers without a target (target columns NULL) and targets with no customers (customer columns NULL).

2. Reconciling two lists

FULL OUTER JOIN is the standard tool for comparing two sources, for example "which target cities are missing customers, and which customer cities are missing targets?":

SELECT COALESCE(c.city, t.city) AS city,
       CASE
         WHEN c.city IS NULL THEN 'Target but no customers'
         WHEN t.city IS NULL THEN 'Customers but no target'
         ELSE 'Both'
       END AS match_status
FROM (SELECT DISTINCT city FROM customers) c
FULL OUTER JOIN city_targets t ON t.city = c.city
ORDER BY match_status, city;

Result:

(showing 12 of 13 rows)

(SELECT DISTINCT city FROM customers) is a subquery used as a table; you'll learn those in the subqueries lesson. COALESCE picks whichever city is not NULL.

CROSS JOIN

Syntax

SELECT ...
FROM table_a
CROSS JOIN table_b;

There's no ON: every row pairs with every row.

3. Every combination

Every category paired with every payment method:

SELECT cat.category_name, pm.payment_method
FROM categories cat
CROSS JOIN (VALUES ('UPI'), ('Card'), ('COD'), ('NetBanking')) AS pm(payment_method)
ORDER BY cat.category_id, pm.payment_method
LIMIT 8;

Result:

(8 rows)

6 categories × 4 methods = 24 rows. VALUES (...) AS pm(payment_method) builds a tiny inline table.

4. Real use: a report grid with zeros

Reports often need a row for every month even when nothing happened. Build the full grid with CROSS JOIN, then LEFT JOIN the actual data onto it:

SELECT m.month::date AS month,
       cat.category_name,
       COUNT(o.order_id) AS lines_sold
FROM generate_series(DATE '2025-01-01', DATE '2025-02-01', INTERVAL '1 month') AS m(month)
CROSS JOIN categories cat
LEFT JOIN products p     ON p.category_id = cat.category_id
LEFT JOIN order_items oi ON oi.product_id = p.product_id
LEFT JOIN orders o       ON o.order_id = oi.order_id
                        AND DATE_TRUNC('month', o.order_date) = m.month
GROUP BY m.month, cat.category_id, cat.category_name
ORDER BY m.month, cat.category_id;

Result:

(12 rows)

generate_series (PostgreSQL) produces one row per month, and the CROSS JOIN gives each month all six categories. The month condition sits in the last ON, so lines from other months don't match and aren't counted, while every category still appears with 0. Without the grid, categories with no sales would simply be missing from the report.

Common mistakes

• Accidental cross joins. Forgetting the ON condition, or writing FROM a, b without a WHERE match, multiplies rows. Two 10,000-row tables become 100 million rows.
• Expecting FULL OUTER JOIN in MySQL. Use LEFT JOIN ... UNION ... RIGHT JOIN instead.
• Filtering a FULL OUTER JOIN in WHERE on one side's columns, which removes the unmatched rows you joined to see.

Practice question

Using city_targets, show each target city with the number of ShopKart customers there, including cities with zero customers.

SELECT t.city,
       t.target_customers,
       COUNT(c.customer_id) AS actual_customers
FROM city_targets t
LEFT JOIN customers c ON c.city = t.city
GROUP BY t.city, t.target_customers
ORDER BY actual_customers DESC, t.city;

Key takeaways

• FULL OUTER JOIN keeps unmatched rows from both tables.
• It's ideal for reconciling two lists; use COALESCE to merge the key columns.
• CROSS JOIN returns every combination: rows in A × rows in B.
• CROSS JOIN + LEFT JOIN builds complete report grids that show zeros.