Other Join Types: Beyond Basic Joins

1
2
3
4
5
6
7

-- Explicit CROSS JOIN syntax (preferred)
SELECT e.employee_name, d.department_name
FROM employees e
CROSS JOIN departments d;
 
-- Each employee is paired with every department
-- If 100 employees × 10 departments = 1,000 result rows

1
2
3
4
5
6
7
8
9
10
11
12
13
14

-- Implicit cross join (legacy syntax)
SELECT e.employee_name, d.department_name
FROM employees e, departments d;
 
-- Functionally identical to CROSS JOIN
-- Still supported but less explicit about intent
 
-- DANGER: Forgetting WHERE clause with comma syntax
-- creates accidental cross join:
SELECT e.employee_name, d.department_name
FROM employees e, departments d
WHERE e.salary > 50000;
-- This still produces a cross join, just with 
-- salary filtering on the employee side

1
2
3
4
5
6
7
8
9
10
11
12
13
14

-- CROSS JOIN with WHERE clause (filtered cross join)
SELECT e.employee_name, p.project_name
FROM employees e
CROSS JOIN projects p
WHERE p.department_id = e.department_id;
 
-- This is functionally equivalent to:
SELECT e.employee_name, p.project_name
FROM employees e
INNER JOIN projects p ON p.department_id = e.department_id;
 
-- However, the query optimizer typically handles 
-- them identically - modern databases recognize 
-- that a CROSS JOIN with WHERE equality is an inner join

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

-- Product configuration: all color-size combinations
SELECT c.color_name, s.size_code, 
       CONCAT(c.color_name, '-', s.size_code) AS sku_suffix
FROM product_colors c
CROSS JOIN product_sizes s
ORDER BY c.color_name, s.size_code;
 
-- Result: Every valid configuration
-- color_name | size_code | sku_suffix
-- ----------------------------------------
-- Blue       | S         | Blue-S
-- Blue       | M         | Blue-M
-- Blue       | L         | Blue-L
-- Red        | S         | Red-S
-- Red        | M         | Red-M
-- Red        | L         | Red-L
-- ...

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

-- Generate all date-product combinations for sales analysis
-- (to show zero sales as actual zeros, not as missing rows)
 
WITH date_range AS (
    SELECT generate_series(
        '2024-01-01'::date,
        '2024-01-31'::date,
        '1 day'::interval
    )::date AS report_date
),
products AS (
    SELECT product_id, product_name 
    FROM products 
    WHERE active = true
)
SELECT 
    d.report_date,
    p.product_id,
    p.product_name,
    COALESCE(s.total_quantity, 0) AS quantity_sold,
    COALESCE(s.total_revenue, 0) AS revenue
FROM date_range d
CROSS JOIN products p
LEFT JOIN (
    SELECT 
        DATE(sale_date) AS sale_date,
        product_id,
        SUM(quantity) AS total_quantity,
        SUM(quantity * unit_price) AS total_revenue
    FROM sales
    GROUP BY DATE(sale_date), product_id
) s ON s.sale_date = d.report_date AND s.product_id = p.product_id
ORDER BY d.report_date, p.product_id;
 
-- This ensures every product appears for every day,
-- even if no sales occurred (showing 0 instead of missing)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

-- Generate test users with realistic-looking names
WITH first_names AS (
    SELECT name FROM (VALUES 
        ('James'), ('Mary'), ('John'), ('Patricia'),
        ('Robert'), ('Jennifer'), ('Michael'), ('Linda'),
        ('William'), ('Elizabeth')
    ) v(name)
),
last_names AS (
    SELECT name FROM (VALUES 
        ('Smith'), ('Johnson'), ('Williams'), ('Brown'),
        ('Jones'), ('Garcia'), ('Miller'), ('Davis'),
        ('Rodriguez'), ('Martinez')
    ) v(name)
)
INSERT INTO test_users (first_name, last_name, email)
SELECT 
    f.name,
    l.name,
    LOWER(f.name || '.' || l.name || '@example.com')
FROM first_names f
CROSS JOIN last_names l;
 
-- Creates 100 realistic test users from 20 seed values
-- (10 first names × 10 last names)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

-- Sales matrix: regions × product categories
-- Even when certain region/category combos have no sales
 
SELECT 
    r.region_name,
    c.category_name,
    COALESCE(SUM(s.amount), 0) AS total_sales
FROM regions r
CROSS JOIN categories c
LEFT JOIN sales s 
    ON s.region_id = r.region_id 
    AND s.category_id = c.category_id
    AND s.sale_date >= '2024-01-01'
    AND s.sale_date < '2024-02-01'
GROUP BY r.region_name, c.category_name
ORDER BY r.region_name, c.category_name;
 
-- Guarantees a row for every region/category pair
-- Essential for accurate reports and visualizations

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

-- SAFEGUARD 1: Always check table sizes first
SELECT 'employees' AS table_name, COUNT(*) AS row_count FROM employees
UNION ALL
SELECT 'departments', COUNT(*) FROM departments;
-- Calculate: employee_count × department_count = result size
 
-- SAFEGUARD 2: Use LIMIT during development
SELECT e.employee_name, d.department_name
FROM employees e
CROSS JOIN departments d
LIMIT 100;  -- Prevents accidental explosion
 
-- SAFEGUARD 3: Query cost estimation (PostgreSQL)
EXPLAIN SELECT e.employee_name, d.department_name
FROM employees e
CROSS JOIN departments d;
-- Check the "rows" estimate before executing
 
-- SAFEGUARD 4: Set statement timeout
SET statement_timeout = '30s';  -- PostgreSQL
-- Query will be killed if it runs too long

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

-- PostgreSQL EXPLAIN example
EXPLAIN ANALYZE SELECT e.*, d.*
FROM employees e
CROSS JOIN departments d;
 
-- Typical plan output:
-- Nested Loop  (cost=0.00..25.00 rows=1000 width=200)
--   -> Seq Scan on employees e (rows=100)
--   -> Materialize (rows=10)
--         -> Seq Scan on departments d
-- 
-- Key indicators:
-- - "Nested Loop" without join condition = CROSS JOIN
-- - rows = 100 × 10 = 1,000 (multiplicative)
-- - Materialize: smaller table cached for repeated access

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

-- INEFFICIENT: Filter after cross join
SELECT e.employee_name, d.department_name
FROM employees e
CROSS JOIN departments d
WHERE d.region = 'West' AND e.status = 'Active';
-- Creates full Cartesian product, then filters
 
-- EFFICIENT: Filter before cross join
SELECT e.employee_name, d.department_name
FROM (SELECT * FROM employees WHERE status = 'Active') e
CROSS JOIN (SELECT * FROM departments WHERE region = 'West') d;
-- Smaller inputs = smaller cross product
 
-- Most optimizers do this automatically, but CTEs make it explicit:
WITH active_employees AS (
    SELECT employee_id, employee_name 
    FROM employees WHERE status = 'Active'
),
west_departments AS (
    SELECT department_id, department_name 
    FROM departments WHERE region = 'West'
)
SELECT e.employee_name, d.department_name
FROM active_employees e
CROSS JOIN west_departments d;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

-- Process in manageable chunks using LIMIT/OFFSET or ID ranges
-- Instead of one massive cross join:
 
-- BATCH 1: First 1000 employees × all departments
INSERT INTO employee_department_matrix
SELECT e.employee_id, d.department_id, ...
FROM employees e
CROSS JOIN departments d
WHERE e.employee_id BETWEEN 1 AND 1000;
 
-- BATCH 2: Next 1000 employees × all departments  
INSERT INTO employee_department_matrix
SELECT e.employee_id, d.department_id, ...
FROM employees e
CROSS JOIN departments d
WHERE e.employee_id BETWEEN 1001 AND 2000;
 
-- Typically implemented in application code with loops

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

-- PostgreSQL: LATERAL join
-- For each product, generate price points
SELECT p.product_id, p.base_price, m.multiplier, 
       p.base_price * m.multiplier AS adjusted_price
FROM products p
CROSS JOIN LATERAL (
    SELECT unnest(ARRAY[0.75, 0.90, 1.00, 1.10, 1.25]) AS multiplier
) m;
 
-- SQL Server: CROSS APPLY
SELECT p.product_id, p.base_price, m.multiplier,
       p.base_price * m.multiplier AS adjusted_price  
FROM products p
CROSS APPLY (
    SELECT multiplier 
    FROM (VALUES (0.75), (0.90), (1.00), (1.10), (1.25)) v(multiplier)
) m;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

-- Look for Nested Loop without join predicate
EXPLAIN (FORMAT TEXT) SELECT * FROM employees e, departments d;
 
-- PostgreSQL output:
-- Nested Loop  (cost=0.00..2637.00 rows=100000 width=200)
--   ->  Seq Scan on employees e  (rows=1000)
--   ->  Materialize  (rows=100)
--         ->  Seq Scan on departments d
 
-- The "Nested Loop" without any filter between tables
-- and rows = (1000 × 100) = 100000 indicates cross join
 
-- Compare with proper inner join:
EXPLAIN (FORMAT TEXT) 
SELECT * FROM employees e 
JOIN departments d ON e.dept_id = d.id;
 
-- Nested Loop  (cost=0.00..123.00 rows=1000 width=200)
--   ->  Seq Scan on employees e  (rows=1000)
--   ->  Index Scan on departments d  (rows=1)
--         Index Cond: (id = e.dept_id)
-- 
-- Note: rows estimate is much smaller, and there's 
-- a join condition (Index Cond)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

-- Before running a new query, check expected vs actual
-- Expected rows for proper join: roughly max(table1, table2)
-- Actual rows for cross join: table1 × table2
 
-- Calculate expected maximum reasonable result
SELECT 
    (SELECT COUNT(*) FROM employees) AS emp_count,
    (SELECT COUNT(*) FROM departments) AS dept_count,
    (SELECT COUNT(*) FROM employees e 
     JOIN departments d ON e.dept_id = d.id) AS join_result;
 
-- If join_result ≈ emp_count × dept_count, you have a cross join
-- If join_result ≈ emp_count (or less), join is probably correct
 
-- Quick sanity check pattern:
WITH query_result AS (
    SELECT e.*, d.* FROM employees e, departments d
    -- Suspicious query here
)
SELECT COUNT(*) FROM query_result;
-- Is this close to emp_count × dept_count? Then it's a cross join.