SQL for Data Analysis Cheat Sheet

Updated 2026-04-29

Next Topic: SQL Server 2025 Cheat Sheet

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 151 indexed concepts. Below is a complete table-by-table outline of this topic, spanning foundational concepts through advanced details.

Table 1: Join TypesTable 2: Aggregate FunctionsTable 3: Statistical Aggregate FunctionsTable 4: Window Functions - RankingTable 5: Window Functions - AnalyticTable 6: Window Functions - Aggregate Over PartitionTable 7: PARTITION BY and Window FramesTable 8: Common Table Expressions (CTEs)Table 9: SubqueriesTable 10: Set OperatorsTable 11: GROUP BY and Aggregation ModifiersTable 12: Conditional LogicTable 13: NULL HandlingTable 14: Filtering TechniquesTable 15: String FunctionsTable 16: Regular ExpressionsTable 17: Date and Time FunctionsTable 18: Numeric FunctionsTable 19: Data Type ConversionTable 20: JSON FunctionsTable 21: Data PivotingTable 22: Query Optimization PatternsTable 23: Time Series PatternsTable 24: Advanced Join Patterns

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
INNER JOIN	`SELECT * FROM orders o` `INNER 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.
LEFT JOIN (LEFT OUTER JOIN)	`SELECT * FROM employees e` `LEFT 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.
RIGHT JOIN (RIGHT OUTER JOIN)	`SELECT * FROM orders o` `RIGHT 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.
FULL OUTER JOIN	`SELECT * FROM sales s` `FULL 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.
CROSS JOIN	`SELECT * FROM colors` `CROSS JOIN sizes`	• Produces Cartesian product — every row from left paired with every row from right • use with care.

Table 1: Join Types

Type	Example	Description
INNER JOIN	`SELECT * FROM orders o` `INNER 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.
LEFT JOIN (LEFT OUTER JOIN)	`SELECT * FROM employees e` `LEFT 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.
RIGHT JOIN (RIGHT OUTER JOIN)	`SELECT * FROM orders o` `RIGHT 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.
FULL OUTER JOIN	`SELECT * FROM sales s` `FULL 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.
CROSS JOIN	`SELECT * FROM colors` `CROSS JOIN sizes`	• Produces Cartesian product — every row from left paired with every row from right • use with care.