SQL Cheat Sheet: Joins, Window Functions and CTEs

When writing production-grade queries, backend engineers and data analysts frequently rely on core relational joins, analytical window functions, and recursive Common Table Expressions (**CTEs**). This cheat sheet provides a quick-reference guide with robust syntax and practical examples for mastering these essential SQL constructs.

1. SQL Joins Reference

Joins combine columns from two or more tables based on a related column between them. Understanding the exact mechanics of each join type prevents data duplication and unexpected row loss.

• `INNER JOIN`: Returns only the records that have matching values in both tables.
• `LEFT (OUTER) JOIN`: Returns all records from the left table, and the matched records from the right table. Unmatched right-side columns return `NULL`.
• `RIGHT (OUTER) JOIN`: Returns all records from the right table, and the matched records from the left table. Unmatched left-side columns return `NULL`.
• `FULL (OUTER) JOIN`: Returns all records when there is a match in either left or right table records.
• `CROSS JOIN`: Produces a Cartesian product of the two tables, returning every possible combination of rows.

-- Comprehensive Join Example
SELECT 
    u.user_id,
    u.username,
    o.order_id,
    o.total_amount
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE o.status = 'COMPLETED';

2. Advanced Window Functions

Window functions perform calculations across partitions of rows without collapsing the underlying result set. Beyond ranking functions, analytical navigation functions like `LEAD()` and `LAG()` are indispensable for time-series analysis.

• `LAG()`: Accesses data from a previous row in the same result set without needing a self-join.
• `LEAD()`: Accesses data from a subsequent row in the same result set.
• `SUM() OVER()`: Computes running totals and moving averages across partitions.

-- Calculating Running Totals and Previous Month Comparison
SELECT 
    transaction_id,
    customer_id,
    amount,
    transaction_date,
    SUM(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date) AS running_total,
    LAG(amount, 1, 0) OVER (PARTITION BY customer_id ORDER BY transaction_date) AS previous_amount
FROM transactions;

3. Common Table Expressions (CTEs)

A **Common Table Expression (CTE)** is a temporary result set defined within the execution scope of a single `SELECT`, `INSERT`, `UPDATE`, or `DELETE` statement. CTEs drastically improve query modularity and are required for recursive queries such as traversing organizational charts or bill-of-materials structures.

• Non-Recursive CTEs: Acts as a cleaner, more maintainable alternative to complex subqueries and inline views.
• Recursive CTEs: Consists of an anchor member (initial query) and a recursive member joined by `UNION ALL`, running iteratively until no rows are returned.

-- Non-Recursive CTE for Aggregated Filtering
WITH HighValueCustomers AS (
    SELECT customer_id, SUM(total_amount) AS total_spent
    FROM orders
    GROUP BY customer_id
    HAVING SUM(total_amount) > 1000
)
SELECT c.name, h.total_spent
FROM customers c
JOIN HighValueCustomers h ON c.id = h.customer_id;

-- Recursive CTE for Category Trees
WITH RECURSIVE CategoryTree AS (
    SELECT id, name, parent_id, 1 AS depth
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id, ct.depth + 1
    FROM categories c
    JOIN CategoryTree ct ON c.parent_id = ct.id
)
SELECT * FROM CategoryTree;

Conclusion

Mastering joins, leveraging analytical window functions, and structuring queries with CTEs allows developers to write highly efficient, readable, and scalable SQL code for complex backend systems.