SQL FULL OUTER JOIN and CROSS JOIN

SELECT columns
FROM table_a
FULL OUTER JOIN table_b ON table_a.column = table_b.column;

    ┌───────────────┐     ┌───────────────┐
    │               │     │               │
    │   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 ──────►

-- 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)

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;

-- 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;

-- 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;

-- 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;

-- 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;

-- 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;

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;

-- 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;

SELECT columns
FROM table_a
CROSS JOIN table_b;

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;

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

-- 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;

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;

-- 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;

-- 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;

-- 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!

-- 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;

-- 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)

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)

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;

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;

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;

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;

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;

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;