Cheatsheets

SQL

SQL queries, joins, aggregation, DDL, and window functions.

36 entries

Query6

SELECT col1, col2 FROM t

Select specific columns

SELECT DISTINCT col FROM t

Unique values only

... ORDER BY col DESC

Sort results (descending)

... LIMIT 10 OFFSET 20

Paginate results

SELECT col AS alias

Rename a column in output

SELECT name, count(*) AS n
FROM orders
GROUP BY name
HAVING count(*) > 5
ORDER BY n DESC;

Full grouped + filtered query

Filtering7

WHERE col = 'x'

Filter rows by a condition

WHERE a AND b / OR

Combine conditions

WHERE col IN (1, 2, 3)

Match any value in a list

WHERE col BETWEEN a AND b

Match a range (inclusive)

WHERE col LIKE 'a%'

Pattern match (% = any chars)

WHERE col IS NULL

Match NULL values

WHERE EXISTS (SELECT 1 FROM ...)

Correlated existence check

Joins5

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

Rows matching in both tables

LEFT JOIN b ON ...

All left rows + matches from right

RIGHT JOIN b ON ...

All right rows + matches from left

FULL OUTER JOIN b ON ...

All rows from both sides

CROSS JOIN b

Cartesian product of both tables

Aggregation6

COUNT(*) / SUM(col) / AVG(col)

Aggregate functions

MIN(col) / MAX(col)

Smallest / largest value

GROUP BY col

Group rows for aggregation

HAVING COUNT(*) > 1

Filter groups (after GROUP BY)

COALESCE(a, b)

First non-NULL value

STRING_AGG(col, ',')

Concatenate grouped values

Modify & DDL7

INSERT INTO t (a) VALUES (1)

Insert a row

UPDATE t SET a = 1 WHERE id = 2

Update rows

DELETE FROM t WHERE id = 2

Delete rows

INSERT INTO t (id, n) VALUES (1, 'a')
ON CONFLICT (id)
DO UPDATE SET n = EXCLUDED.n;

Upsert (Postgres)

CREATE TABLE t (id INT PRIMARY KEY)

Create a table

ALTER TABLE t ADD COLUMN c TEXT

Add a column

CREATE INDEX idx ON t(col)

Create an index

Window & CTE5

ROW_NUMBER() OVER (ORDER BY col)

Sequential row number

RANK() OVER (PARTITION BY a ORDER BY b)

Rank within partitions

SUM(x) OVER (PARTITION BY a)

Aggregate without collapsing rows

LAG(col) OVER (ORDER BY t)

Value from the previous row

WITH recent AS (
  SELECT * FROM orders
  WHERE created > now() - interval '7 days'
)
SELECT * FROM recent;

Common table expression (CTE)