A quick-reference for SQL syntax, functions, and behavior across PostgreSQL, MySQL, and SQL Server. Organized for lookup, not for reading.
SELECT Clause Order
Clause
Purpose
Evaluated
SELECT
Columns to return
5th
FROM
Tables to query
1st
JOIN
Combine tables
2nd
WHERE
Filter rows
3rd
GROUP BY
Group rows
4th
HAVING
Filter groups
6th
ORDER BY
Sort result
7th
LIMIT / OFFSET
Limit rows
8th
WHERE cannot use aliases defined in SELECT; ORDER BY can.
JOIN Types
Join
Returns
INNER JOIN
Rows matching in both tables
LEFT JOIN
All left rows + matching right (NULL if none)
RIGHT JOIN
All right rows + matching left
FULL OUTER JOIN
All rows from both tables
CROSS JOIN
Cartesian product
NATURAL JOIN
Join on same-named columns
SELF JOIN
Table joined to itself
-- LEFT JOIN with NULL handling
SELECT u.id, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL; -- users with no orders
Aggregation
Function
Returns
COUNT(*)
Row count
COUNT(col)
Non-NULL count
COUNT(DISTINCT col)
Unique non-NULL count
SUM(col)
Sum
AVG(col)
Average
MIN(col) / MAX(col)
Min/max
STRING_AGG(col, ',')
Concatenate (PostgreSQL)
GROUP_CONCAT(col)
Concatenate (MySQL)
ARRAY_AGG(col)
Array (PostgreSQL)
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 5;
Window Functions
Function
Purpose
ROW_NUMBER()
Sequential number, no ties
RANK()
Rank with gaps for ties
DENSE_RANK()
Rank without gaps
NTILE(n)
Divide into n buckets
LAG(col, n)
Previous row value
LEAD(col, n)
Next row value
SUM(...) OVER (...)
Running total
FIRST_VALUE(col)
First value in frame
LAST_VALUE(col)
Last value in frame
SELECT
user_id,
total,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM orders;
-- Top-1 per group
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) rn
FROM orders
) t WHERE rn = 1;
CTEs and Recursion
-- Basic CTE
WITH recent_orders AS (
SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '7 days'
)
SELECT user_id, COUNT(*) FROM recent_orders GROUP BY user_id;
-- Recursive CTE
WITH RECURSIVE tree AS (
SELECT id, parent_id, 1 AS depth FROM nodes WHERE parent_id IS NULL
UNION ALL
SELECT n.id, n.parent_id, t.depth + 1
FROM nodes n JOIN tree t ON n.parent_id = t.id
)
SELECT * FROM tree;
DML
Statement
Purpose
INSERT INTO t (cols) VALUES (...)
Insert
INSERT INTO t SELECT ...
Insert from query
UPDATE t SET col = val WHERE ...
Update
DELETE FROM t WHERE ...
Delete
TRUNCATE TABLE t
Fast delete all rows
MERGE INTO t USING s ON ...
Upsert (SQL Server, Oracle, PostgreSQL 15+)
Upsert
-- PostgreSQL
INSERT INTO users (id, name) VALUES (1, 'Alice')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;
-- MySQL
INSERT INTO users (id, name) VALUES (1, 'Alice')
ON DUPLICATE KEY UPDATE name = VALUES(name);
UPDATE FROM
-- PostgreSQL
UPDATE orders o
SET total = p.price * o.qty
FROM products p
WHERE o.product_id = p.id;
DDL
Statement
Purpose
CREATE TABLE
New table
ALTER TABLE ... ADD COLUMN
Add column
ALTER TABLE ... DROP COLUMN
Remove column
ALTER TABLE ... ALTER COLUMN
Change type/default
CREATE INDEX
Index
CREATE INDEX CONCURRENTLY
Non-blocking index (PostgreSQL)
DROP TABLE
Remove table
DROP TABLE IF EXISTS
Safe remove
TRUNCATE
Fast empty
-- Concurrent index build (PostgreSQL)
CREATE INDEX CONCURRENTLY idx_orders_user ON orders (user_id);
-- Add NOT NULL column with default
ALTER TABLE users ADD COLUMN active boolean DEFAULT true NOT NULL;
Constraints
Constraint
Purpose
PRIMARY KEY
Unique + not null
UNIQUE
Unique (nullable)
NOT NULL
Non-null
CHECK (cond)
Validation
FOREIGN KEY
Referential integrity
DEFAULT val
Default value
GENERATED ALWAYS AS (...) STORED
Computed column
CREATE TABLE orders (
id bigserial PRIMARY KEY,
user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
total numeric(12,2) CHECK (total >= 0),
created_at timestamptz DEFAULT now()
);
Data Types
Type
Use
integer / bigint
Whole numbers
numeric(p,s)
Exact decimal
real / double precision
Floating point
text / varchar(n)
Strings
boolean
True/false
date
Date
timestamptz
Timestamp with timezone
interval
Duration
json / jsonb
JSON (jsonb is binary)
uuid
UUID
array
Arrays (PostgreSQL)
enum
Enumerated values
String Functions
Function
Purpose
LENGTH(s)
Length
UPPER(s) / LOWER(s)
Case
TRIM(s)
Strip whitespace
SUBSTRING(s FROM a FOR b)
Substring
REPLACE(s, from, to)
Replace
CONCAT(a, b) or a || b
Concatenate
SPLIT_PART(s, delim, n)
Split
REGEXP_REPLACE(s, pattern, repl)
Regex replace
LIKE 'pat%'
Pattern match (% any, _ one)
ILIKE
Case-insensitive LIKE (PostgreSQL)
Date/Time Functions
Function
Purpose
NOW() / CURRENT_TIMESTAMP
Current time
CURRENT_DATE
Current date
AGE(ts1, ts2)
Interval
DATE_TRUNC('month', ts)
Truncate
EXTRACT(YEAR FROM ts)
Extract part
ts + INTERVAL '1 day'
Add interval
ts1 - ts2
Interval difference
SELECT DATE_TRUNC('month', created_at) AS month, COUNT(*)
FROM orders
GROUP BY 1
ORDER BY 1;
JSON (PostgreSQL)
Operation
Syntax
Access field
col->'key' (returns json)
Access field as text
col->>'key'
Access nested
col->'a'->>'b'
Array element
col->0
Contains
col @> '{"a": 1}'
Key exists
col ? 'key'
Filter
WHERE col->>'status' = 'active'
SELECT id, data->>'name' AS name
FROM events
WHERE data @> '{"type": "click"}';
Transactions
Statement
Purpose
BEGIN / START TRANSACTION
Start
COMMIT
Commit
ROLLBACK
Abort
SAVEPOINT name
Set savepoint
ROLLBACK TO name
Rollback to savepoint
RELEASE SAVEPOINT name
Release savepoint
Isolation Levels
Level
Dirty Read
Non-Repeatable
Phantom
READ UNCOMMITTED
Yes
Yes
Yes
READ COMMITTED
No
Yes
Yes
REPEATABLE READ
No
No
Yes*
SERIALIZABLE
No
No
No
*PostgreSQL’s REPEATABLE READ also prevents phantoms.
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- ...
COMMIT;
Locking
Lock
Syntax
Row lock
SELECT ... FOR UPDATE
Row lock, skip locked
SELECT ... FOR UPDATE SKIP LOCKED
Row lock, no wait
SELECT ... FOR UPDATE NOWAIT
Table lock
LOCK TABLE t IN ACCESS EXCLUSIVE MODE
-- Job queue pattern
SELECT * FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
Indexes
Index
Best For
B-tree
Equality, range, sort
Hash
Equality only
GIN
Arrays, JSONB, full-text
GiST
Geometric, range types
BRIN
Large sorted tables
HNSW (pgvector)
Vector similarity
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC);
CREATE INDEX idx_events_data ON events USING gin (data);
Execution Plan
Command
Purpose
EXPLAIN query
Estimated plan
EXPLAIN ANALYZE query
Actual plan with timing
EXPLAIN (ANALYZE, BUFFERS) query
With I/O stats
EXPLAIN (VERBOSE) query
With output columns
Node
Meaning
Seq Scan
Full table scan
Index Scan
Index lookup + table fetch
Index Only Scan
Index-only (no table fetch)
Bitmap Heap Scan
Index bitmap + heap fetch
Hash Join
Build hash table, probe
Nested Loop
For each outer row, scan inner
Merge Join
Sorted merge
Common Patterns
Pagination
-- Offset (slow on deep pages)
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 100;
-- Keyset (fast)
SELECT * FROM orders WHERE id > 1000 ORDER BY id LIMIT 20;
Deduplication
-- Keep the latest row per user
DELETE FROM orders o
USING orders o2
WHERE o.user_id = o2.user_id
AND o.created_at < o2.created_at;
Running Total
SELECT
id,
total,
SUM(total) OVER (ORDER BY created_at) AS running_total
FROM orders;
Percent of Total
SELECT
category,
SUM(amount)::numeric / SUM(SUM(amount)) OVER () AS pct
FROM sales
GROUP BY category;
Find Duplicates
SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
Top N per Group
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) rn
FROM products
) t WHERE rn <= 3;
Gotchas
Trap
Reality
NULL = NULL
Returns NULL, not true; use IS NULL
COUNT(col) counts NULLs
No; use COUNT(*) for row count
WHERE can use SELECT aliases
No; ORDER BY can
NOT IN with NULL
Returns no rows; use NOT EXISTS
TRUNCATE is transactional
Yes in PostgreSQL, no in MySQL
VARCHAR(n) truncates
No; errors on overflow (PostgreSQL)
Implicit type casting
Can prevent index use
SELECT * in production
Fetches unused columns; breaks on schema change
ORDER BY without LIMIT
Sorts full result set
LIKE '%x%'
Cannot use B-tree index
OR in WHERE
Can prevent index use; use UNION
DISTINCT is slow
Sorts; consider GROUP BY or index
UPDATE without WHERE
Updates all rows
DELETE without WHERE
Deletes all rows
COUNT(*) on large table
Full scan; use estimate for large tables
Quick Decision Tables
Which JOIN?
Need
Use
Only matching rows
INNER JOIN
All from one side
LEFT JOIN
All from both
FULL OUTER JOIN
Cartesian
CROSS JOIN
Which pagination?
Need
Use
Random page access
OFFSET
Deep pages, stable order
Keyset
Cursor API
Keyset
Which index?
Query
Index
WHERE col = val
B-tree
WHERE col @> json
GIN
ORDER BY col
B-tree
Vector similarity
HNSW
Large append-only
BRIN
Which isolation?
Need
Level
Read-only analytics
READ COMMITTED
Consistent reads within tx
REPEATABLE READ
Strict serializability
SERIALIZABLE
One-Page Flow
QUERY
FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
WRITE
BEGIN → INSERT/UPDATE/DELETE → COMMIT (or ROLLBACK)
SCHEMA
CREATE TABLE → constraints → CREATE INDEX → seed data
OPTIMIZE
EXPLAIN ANALYZE → identify slow node → add index → re-measure
TRANSACTIONS
BEGIN → operations → SAVEPOINT → ROLLBACK TO → COMMIT
Was this helpful?
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for: ✅ Finance (Mortgage, Interest, Inflation) ✅ Tech (Base64, JSON, Dev Suite, IP) ✅ Health (BMI, BMR, TDEE) ✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private 🔒 No data leaves your device 💎 100% Free