SQL

SQL query reference: SELECT, JOINs, aggregation, subqueries, CTEs, window functions, data modification, and schema commands with practical examples.

Cheat Sheet

SQL (Structured Query Language) is the standard language for managing and querying relational databases. While each database has its own dialect (PostgreSQL, MySQL, SQLite, SQL Server…), the core syntax shown here works across all major engines.

Note: Dialects differ. Auto-increment (SERIAL vs AUTO_INCREMENT), date functions, and string concatenation (|| vs +) vary between engines. Examples use the most portable form.

Query Basics

Clause	Example	What it does
SELECT	SELECT name, email FROM users	Retrieve specific columns from a table.
WHERE	WHERE age >= 18 AND status = 'active'	Filter rows based on conditions. Supports AND / OR / NOT, comparisons, IN, BETWEEN, LIKE.
ORDER BY	ORDER BY created_at DESC, name ASC	Sort results. ASC (default) or DESC. Multiple columns = multi-level sort.
LIMIT	LIMIT 10 OFFSET 20	Limit the number of rows returned. OFFSET skips rows (for pagination).
DISTINCT	SELECT DISTINCT category FROM products	Return only unique rows. Duplicate values are collapsed to one.
AS (alias)	SELECT COUNT(*) AS total FROM orders	Rename a column or table for the query output (alias).

JOINs

SELECT * FROM a INNER JOIN b ON a.id = b.a_id

INNER JOIN

Returns rows where the condition matches in BOTH tables. Most common join.

SELECT * FROM a LEFT JOIN b ON a.id = b.a_id

LEFT JOIN

Returns ALL rows from the left table, plus matched rows from the right. Missing matches = NULL.

SELECT * FROM a RIGHT JOIN b ON a.id = b.a_id

RIGHT JOIN

Returns ALL rows from the right table, plus matched rows from the left.

SELECT * FROM a FULL JOIN b ON a.id = b.a_id

FULL OUTER JOIN

Returns all rows from both tables. NULL where no match exists.

SELECT * FROM a CROSS JOIN b

CROSS JOIN

Cartesian product: every row of A × every row of B. Use with caution.

SELECT e.name, m.name FROM emp e JOIN emp m ON e.mgr_id = m.id

SELF JOIN

A table joined with itself. Useful for hierarchies (e.g., employees + managers).

Aggregation & Grouping

Function	Example	What it does
COUNT	COUNT(*) / COUNT(DISTINCT col)	Counts rows or non-null values. COUNT(*) includes NULLs; COUNT(col) excludes them.
SUM	SUM(amount)	Sum of numeric values in a column.
AVG	AVG(price)	Average (mean) of numeric values.
MIN / MAX	MIN(price), MAX(price)	Smallest and largest value in a column. Works with text and dates too.
GROUP BY	GROUP BY category	Groups rows by column value. Must be used with aggregate functions. Every non-aggregated column in SELECT must be in GROUP BY.
HAVING	HAVING COUNT(*) > 5	Filters groups AFTER aggregation (WHERE filters rows BEFORE). Required for conditions on aggregate results.

Common Functions & Expressions

Expression	Example	What it does
CASE	CASE WHEN status = 'active' THEN 'on' ELSE 'off' END	Inline conditional. Evaluates WHENs in order and returns the first true branch. The simple form (CASE col WHEN value) works for equality checks.
COALESCE	COALESCE(email, phone, 'none')	Returns the first non-NULL argument. The standard way to supply fallback values for missing fields in reports.
NULLIF	total / NULLIF(items, 0)	Returns NULL when both arguments are equal. The clean way to avoid divide-by-zero: the division yields NULL instead of erroring.
IS NULL	WHERE deleted_at IS NULL	NULL comparisons require IS NULL / IS NOT NULL. Using = NULL evaluates to unknown and matches nothing.
CAST	CAST(total AS DECIMAL(10,2))	Explicit type conversion. PostgreSQL also supports the shorthand :: (e.g., total::numeric).
String fns	UPPER(name), LOWER(name), CONCAT(a, b), LENGTH(str)	Case conversion, concatenation, and length. \|\| is the concat operator in PostgreSQL/SQLite; SQL Server uses +. LENGTH is LEN() there.
Date fns	CURRENT_DATE, NOW(), DATE_TRUNC('month', created_at)	Current date/time and date arithmetic. DATE_TRUNC is PostgreSQL; MySQL uses YEAR()/MONTH()/DATE_FORMAT().

Subqueries & CTEs

Scalar Subquery (single value)

SELECT name, (SELECT MAX(price) FROM products) AS max_price
FROM users;

Returns a single value used in an expression. Must return exactly one row and one column.

Row Subquery (IN / EXISTS)

SELECT * FROM orders
WHERE customer_id IN (
  SELECT id FROM customers
  WHERE status = 'vip'
);

Use IN for list-of-values results. EXISTS is more efficient for large datasets: it returns true/false per outer row.

CTE (WITH clause)

WITH sales_cte AS (
  SELECT region, SUM(amount) AS total
  FROM orders
  GROUP BY region
)
SELECT * FROM sales_cte
WHERE total > 10000;

A named temporary result set. Makes complex queries readable. Can reference itself recursively (e.g., for trees).

Recursive CTE (tree hierarchy)

WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS level
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, org.level + 1
  FROM employees e
  JOIN org ON e.manager_id = org.id
)
SELECT * FROM org;

Walks a tree structure level by level. Anchor = root(s), recursive step = children. UNION ALL stops when no new rows found.

Window Functions

Function	Example	What it does
ROW_NUMBER()	ROW_NUMBER() OVER (ORDER BY score DESC)	Assigns a unique sequential integer to each row within the partition, starting at 1.
RANK()	RANK() OVER (ORDER BY score DESC)	Like ROW_NUMBER but ties share same rank. The next rank skips values (1, 1, 3).
DENSE_RANK()	DENSE_RANK() OVER (ORDER BY score DESC)	Like RANK but without gaps. Ties share same rank, next is the following integer (1, 1, 2).
LAG()	LAG(amount, 1) OVER (ORDER BY date)	Access the value from the previous row within the partition. Great for day-over-day comparisons.
LEAD()	LEAD(amount, 1) OVER (ORDER BY date)	Access the value from the next row within the partition.
FIRST_VALUE()	FIRST_VALUE(amount) OVER (ORDER BY date)	Returns the first value in the ordered window frame.
NTILE(n)	NTILE(4) OVER (ORDER BY score DESC)	Divides rows into n buckets (roughly equal size). Useful for quartiles/percentiles.

Data Modification

Command	Example	What it does
INSERT	INSERT INTO users (name, email) VALUES ('Alice', 'a@b.com')	Adds a new row. Can insert multiple rows in one statement with comma-separated VALUES.
UPDATE	UPDATE users SET email = 'new@b.com' WHERE id = 1	Modifies existing rows. Always add a WHERE clause: UPDATE without WHERE updates ALL rows.
DELETE	DELETE FROM users WHERE last_login IS NULL	Removes rows matching the condition. DELETE without WHERE empties the table.
UPSERT	INSERT INTO users (id, name) VALUES (1, 'Bob') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name	PostgreSQL-style insert-or-update. MySQL uses INSERT … ON DUPLICATE KEY UPDATE. SQLite uses INSERT OR REPLACE.
TRUNCATE	TRUNCATE TABLE logs	Deletes ALL rows quickly (cannot be filtered). Resets storage and is faster than DELETE for full table clears.

Schema & DDL

Command	Example	What it does
CREATE TABLE	CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE)	Creates a new table with columns, types, and constraints.
ALTER TABLE	ALTER TABLE users ADD COLUMN age INT DEFAULT 0	Modifies an existing table: add/drop columns, change types, rename, add constraints.
DROP TABLE	DROP TABLE IF EXISTS temp_data	Removes a table and all its data permanently.
CREATE INDEX	CREATE INDEX idx_email ON users (email)	Speeds up queries on the indexed column(s). Composite indexes work on multi-column queries.
CREATE VIEW	CREATE VIEW active_users AS SELECT * FROM users WHERE status = 'active'	A saved query that behaves like a virtual table. Great for encapsulating complex logic.
Constraints	NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT	Enforce data integrity at the schema level. CHECK validates a boolean expression. DEFAULT sets a fallback value.

Try our SQL Formatter →

Format, beautify, and lint your SQL queries instantly.