SQL Queries Cheatsheet: SELECT, JOINs & Indexing

Basic SELECT

Most queries start with SELECT, FROM, and WHERE. Always list columns explicitly — SELECT * hurts performance and breaks when schemas change.

-- Top 10 paying customers in 2026
SELECT id, full_name, total_spent, created_at
FROM customers
WHERE country = 'US'
  AND created_at >= '2026-01-01'
ORDER BY total_spent DESC
LIMIT 10;

Clause	Purpose	Example
WHERE	Filter rows before grouping	WHERE status = 'active'
ORDER BY	Sort results	ORDER BY created_at DESC
LIMIT / OFFSET	Paginate	LIMIT 20 OFFSET 40
DISTINCT	Deduplicate	SELECT DISTINCT country FROM customers

Filtering: WHERE Like a Pro

-- Pattern matching, ranges, and lists
SELECT * FROM products
WHERE (price BETWEEN 10 AND 100)
  AND (name ILIKE '%keyboard%' OR sku LIKE 'KB-%')
  AND category_id IN (3, 5, 9)
  AND deleted_at IS NULL;

Edge case: NULL never equals anything — not even NULL. Use IS NULL / IS NOT NULL. WHERE col != 'x' silently drops NULL rows.

JOINs Demystified

SELECT o.id, c.full_name, p.name AS product, oi.qty
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id
LEFT JOIN order_items oi ON oi.order_id = o.id
LEFT JOIN products p ON p.id = oi.product_id
WHERE o.status = 'paid';

JOIN	Keeps	Use when
INNER JOIN	Only matches	You need rows present in both tables
LEFT JOIN	All left rows	Optional relations (profile, coupon)
RIGHT JOIN	All right rows	Rare; prefer LEFT with swapped order
FULL OUTER JOIN	Everything	Reconciliation / diff reports

Best practice: filter in the ON clause for outer joins when the filter applies to the optional side:

-- Correct: keeps customers without recent orders
SELECT c.full_name, o.id
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id AND o.created_at >= '2026-01-01';

Aggregation with GROUP BY

SELECT country, COUNT(*) AS customers, AVG(total_spent) AS avg_spent
FROM customers
WHERE deleted_at IS NULL
GROUP BY country
HAVING COUNT(*) > 100
ORDER BY avg_spent DESC;

Remember the pipeline order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. You cannot use a SELECT alias in WHERE, but you can in ORDER BY.

Window Functions

Window functions compute rankings and running totals without collapsing rows:

SELECT
  full_name,
  department,
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
  SUM(salary) OVER (PARTITION BY department) AS dept_payroll
FROM employees;

Upserts and Safe Writes

-- PostgreSQL upsert
INSERT INTO users (email, full_name, updated_at)
VALUES ('ada@example.com', 'Ada', NOW())
ON CONFLICT (email)
DO UPDATE SET full_name = EXCLUDED.full_name, updated_at = NOW();

-- Parametrized update from JS (never interpolate!)
await db.query(
  'UPDATE products SET price = $1 WHERE id = $2',
  [newPrice, productId]
);

Indexing Essentials

Rule	Why
Index foreign keys and WHERE/JOIN/ORDER BY columns	Avoids full table scans
Prefer composite indexes with leftmost-prefix order	(tenant_id, created_at) serves both filters
Avoid indexing low-cardinality booleans alone	Planner will ignore them anyway
Use EXPLAIN ANALYZE before adding indexes	Confirms the bottleneck is real

CREATE INDEX CONCURRENTLY idx_orders_customer_created
  ON orders (customer_id, created_at DESC);

Common Pitfalls

1. N+1 queries — fetching orders then querying items per order in a loop. Fix with a single JOIN or WHERE id IN (...).
2. Time zones — store timestamptz (UTC), convert on display. DATE(created_at) uses the session time zone.
3. Case sensitivity — use ILIKE (Postgres) or COLLATE NOCASE (SQLite) for user search.
4. Pagination drift — OFFSET skips rows inconsistently under writes. For feeds use keyset pagination: WHERE id < $lastId ORDER BY id DESC LIMIT 20.