LeetCode SQL - Missing Values

Table of contents

FULL OUTER JOIN vs CROSS JOIN

Quick Comparison

FULL OUTER JOIN

Concept

Combines LEFT JOIN + RIGHT JOIN: keeps all rows from both tables, filling unmatched sides with NULL.

Syntax

SELECT *
FROM table_a a
FULL OUTER JOIN table_b b ON a.id = b.id

When to Use

• Find all records and identify gaps (what’s in A but not B, and vice versa)
• Reconciliation: comparing two datasets
• Reporting: show all entities even if data is missing on one side

Example: Student-Course Enrollment

-- Find all students AND all courses, show who's enrolled where
SELECT 
    s.student_name,
    c.course_name,
    CASE 
        WHEN s.student_id IS NULL THEN 'No students enrolled'
        WHEN c.course_id IS NULL THEN 'Not enrolled in any course'
        ELSE 'Enrolled'
    END AS status
FROM
	Students s
	FULL OUTER JOIN Enrollments e ON s.student_id = e.student_id
	FULL OUTER JOIN Courses c ON e.course_id = c.course_id

Result: All students (even unenrolled) + all courses (even with no students)

Handling Missing Values

-- Use COALESCE to handle NULLs
SELECT 
    COALESCE(a.id, b.id) AS id,  -- Use whichever is not NULL
    COALESCE(a.value, 0) AS a_value,
    COALESCE(b.value, 0) AS b_value
FROM table_a a
FULL OUTER JOIN table_b b ON a.id = b.id

CROSS JOIN

Concept

Creates a Cartesian product: every row from table A paired with every row from table B. No matching condition, no NULLs.

Syntax

-- Explicit syntax
SELECT *
FROM table_a
CROSS JOIN table_b

-- Implicit syntax (same result)
SELECT *
FROM table_a, table_b

When to Use

• Generate all combinations: testing scenarios, date ranges
• Create reference grids: all products × all stores
• Expand data: pair every item with every category

Example: Generate Date Range per User

-- Create a row for each user for each day in a range
SELECT 
    u.user_id,
    d.date,
    COALESCE(a.activity_count, 0) AS activities
FROM 
	Users u
	CROSS JOIN (
	    SELECT generate_series('2024-01-01'::date, '2024-01-07'::date, '1 day'::interval)::date AS date
	) d
	LEFT JOIN Activities a ON u.user_id = a.user_id
		AND a.activity_date = d.date

Result: Every user gets a row for every date (7 users × 7 days = 49 rows)

No Missing Values

CROSS JOIN never produces NULLs from the join itself (both sides always have values). NULLs only appear from subsequent LEFT JOINs or original data.

Side-by-Side Example

Data Setup

-- Table A
id | name
1  | Alice
2  | Bob

-- Table B
id | score
2  | 95
3  | 87

FULL OUTER JOIN

SELECT *
FROM 
	A
	FULL OUTER JOIN B ON A.id = B.id

Result (3 rows):

A.id | name  | B.id | score
1    | Alice | NULL | NULL   -- Alice has no score
2    | Bob   | 2    | 95     -- Match
NULL | NULL  | 3    | 87     -- Score with no person

CROSS JOIN

SELECT *
FROM
	A
	CROSS JOIN B

Result (4 rows):

A.id | name  | B.id | score
1    | Alice | 2    | 95
1    | Alice | 3    | 87
2    | Bob   | 2    | 95
2    | Bob   | 3    | 87

Dealing with Missing Values: Strategies

FULL OUTER JOIN + NULL Handling

SELECT 
    COALESCE(a.id, b.id) AS id,
    COALESCE(a.value, 'Missing') AS a_value,
    COALESCE(b.value, 'Missing') AS b_value,
    CASE 
        WHEN a.id IS NULL THEN 'Only in B'
        WHEN b.id IS NULL THEN 'Only in A'
        ELSE 'In both'
    END AS source
FROM 
	table_a a
	FULL OUTER JOIN table_b b ON a.id = b.id

CROSS JOIN + Conditional Aggregation

-- Fill missing dates with 0 activity
SELECT 
    u.user_name,
    d.date,
    COUNT(a.activity_id) AS activity_count  -- 0 if no match
FROM
	Users u
	CROSS JOIN date_series d
	LEFT JOIN Activities a ON u.user_id = a.user_id 
	    AND a.date = d.date
GROUP BY u.user_name, d.date

Decision Guide

Use FULL OUTER JOIN when:

• ✅ You have two related datasets to reconcile
• ✅ You need to identify what’s in A only, B only, or both
• ✅ You want to preserve all rows from both sides
• ✅ Result size: manageable (sum of both tables)

Use CROSS JOIN when:

• ✅ You need every possible combination
• ✅ You’re generating reference data (calendars, grids)
• ✅ You’re expanding sparse data to dense format
• ⚠️ Warning: Result size can explode (10 × 10,000 = 100,000 rows)

Key Takeaway

FULL OUTER JOIN: "Show me everything, mark what's missing"
CROSS JOIN:      "Show me all combinations, nothing is missing"

FULL OUTER JOIN

1965. Employees With Missing Information

SELECT
    COALESCE(e.employee_id, s.employee_id) AS employee_id
FROM
    Employees e
    FULL OUTER JOIN Salaries s ON e.employee_id = s.employee_id
WHERE
    e."name" IS NULL
    OR s.salary IS NULL
ORDER BY employee_id

1934. Confirmation Rate

See LeetCode SQL Analysis - 1934. Confirmation Rate

CROSS JOIN

1280. Students and Examinations

One pass

SELECT
    st.student_id,
    st.student_name,
    su.subject_name,
    COUNT(e.subject_name) AS attended_exams
FROM
    Students st
    CROSS JOIN Subjects su
    LEFT JOIN Examinations e ON st.student_id = e.student_id
    AND su.subject_name = e.subject_name
GROUP BY
    st.student_id,
    st.student_name,
    su.subject_name
ORDER BY
    student_id,
    subject_name

UNION/UNION ALL

1435. Create a Session Bar Chart

See LeetCode SQL Time Series - 1435. Create a Session Bar Chart

1907. Count Salary Categories

WITH salary_categories AS (
    SELECT 'Low Salary' AS category
    UNION 
    SELECT 'Average Salary'
    UNION 
    SELECT 'High Salary'
), account_categories AS (
    SELECT
        CASE
            WHEN income < 20000 THEN 'Low Salary'
            WHEN income <= 50000 THEN 'Average Salary'
            ELSE 'High Salary'
        END AS category
    FROM
        Accounts
)
SELECT
    s.category,
    COUNT(a.category) AS accounts_count
FROM
    salary_categories s
    LEFT JOIN account_categories a USING (category)
GROUP BY category

Postgresql Sytax Sugar

WITH
    cte1 (category, category_order) AS (
        SELECT 'Low Salary', 1
        UNION
        SELECT 'Average Salary', 2
        UNION
        SELECT 'High Salary', 3
    ),
    cte2 AS (
        SELECT
            CASE
                WHEN income < 20000 THEN 'Low Salary'
                WHEN income < 50000 THEN 'Average Salary'
                ELSE 'High Salary'
            END AS category,
            COUNT(*) AS accounts_count
        FROM Accounts
        GROUP BY category
    )
SELECT
    cte1.category,
    COALESCE(cte2.accounts_count, 0) AS accounts_count
FROM
    cte1
    LEFT JOIN cte2 USING (category)
ORDER BY category_order