SQL (Structured Query Language) is the universal language for interacting with relational databases, enabling analysts to extract, aggregate, and transform data from structured tables with unparalleled precision. For data analysis, SQL's true power lies not just in retrieving rows but in window functions, CTEs, joins, statistical aggregates, and JSON processing—tools that turn raw tables into insights. Unlike procedural programming, SQL operates in declarative sets: you describe what you want, and the query optimizer figures out how. Master these patterns, and you'll unlock everything from trend analysis to hierarchical traversals without leaving the database.
What This Cheat Sheet Covers
This topic spans 24 focused tables and 162 indexed concepts, 134 flashcards. Below is a complete table-by-table outline of this topic, spanning foundational concepts through advanced details.
A jump-to index of every table row in this cheat sheet.
An interactive map of every table and concept in this topic.
Table 1: Join Types
Joins are how you stitch rows from separate tables back into a single result, and the kind you pick decides which unmatched rows survive. Beyond the everyday INNER and LEFT joins, this slice reaches into the analyst's heavier artillery — anti-joins that surface what's missing, LATERAL and APPLY for per-row correlated lookups, and the self-join trick that walks hierarchies like employee-to-manager chains.
| Type | Example | Description | |
|---|---|---|---|
SELECT * FROM orders oINNER JOIN customers c ON o.customer_id = c.id | Returns only rows with matching values in both tables — most common join for relating normalized data. | ||
SELECT * FROM employees eLEFT JOIN departments d ON e.dept_id = d.id | Returns all rows from left table, plus matched rows from right — unmatched right-side columns are NULL. | ||
SELECT * FROM orders oRIGHT JOIN products p ON o.product_id = p.id | Returns all rows from right table, plus matched rows from left — mirrors LEFT JOIN with table positions swapped. | ||
SELECT * FROM sales sFULL OUTER JOIN refunds r ON s.order_id = r.order_id | Returns all rows from both tables — unmatched rows from either side show NULL for missing columns. | ||
SELECT * FROM colorsCROSS JOIN sizes | • Produces Cartesian product — every row from left paired with every row from right • use with care. | ||
SELECT e.name, m.name AS managerFROM employees eJOIN employees m ON e.manager_id = m.id | Joins table to itself using aliases — essential for hierarchical data like employee-manager relationships. | ||
SELECT c.*, recent.order_dateFROM customers cLATERAL (SELECT order_date FROM orders WHERE customer_id = c.id ORDER BY order_date DESC LIMIT 1) recent | Subquery in FROM references earlier tables — acts like correlated subquery but returns multiple rows/columns (PostgreSQL, Snowflake). | ||
SELECT e.* FROM employees eLEFT JOIN terminations t ON e.id = t.emp_idWHERE t.emp_id IS NULL | Returns rows from left table with no match in right — filters out unwanted records using NULL check. | ||
SELECT c.name, top_o.amountFROM customers cCROSS APPLY (SELECT TOP 1 amount FROM orders WHERE customer_id = c.id ORDER BY amount DESC) top_o | • SQL Server / Azure equivalent of LATERAL JOIN — • CROSS APPLY returns only rows where subquery produces results • OUTER APPLY includes rows even when subquery returns nothing. | ||
SELECT * FROM ordersNATURAL JOIN customers | • Automatically joins on all columns with same name — implicit and fragile • explicit ON clauses preferred. |