SQL remains the most universally useful skill a developer can have in 2026. Whether you are querying Postgres, MySQL, SQLite, DuckDB, or a cloud warehouse, the core language is the same. This guide moves from basic SELECT to window functions with runnable examples throughout.
What changed in 2026
- DuckDB went mainstream for analytical queries on local files — the same SQL you know runs on Parquet, CSV, and JSON directly.
- Postgres 17 added more parallel query improvements — queries that used sequential scans on 16-core machines are noticeably faster without any schema changes.
- AI assistants generate plausible but wrong SQL — understanding the plan yourself is more valuable than ever because LLM output needs verification.
- Window functions are no longer "advanced" — any developer working with time-series or ranked results is expected to know them.
The anatomy of a SELECT
SELECT column1, column2, aggregate(column3) -- 5. what to return
FROM table_name -- 1. source
JOIN other_table ON condition -- 2. join
WHERE row_filter -- 3. filter rows
GROUP BY column1, column2 -- 4. aggregate
HAVING aggregate_filter -- 6. filter groups
ORDER BY column1 DESC -- 7. sort
LIMIT 100; -- 8. cap rows
The numbers show logical execution order — not the written order. WHERE runs before GROUP BY; HAVING runs after. That matters when you wonder why you can't filter on an alias defined in SELECT.
JOINs explained
-- INNER: only rows that match in both tables
SELECT o.id, c.name
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id;
-- LEFT: every row from orders, NULL for missing customers
SELECT o.id, c.name
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id;
-- Count orders per customer (including customers with zero orders)
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name
ORDER BY order_count DESC;
| JOIN type |
Returns |
| INNER JOIN |
Rows with a match in both tables |
| LEFT JOIN |
All left rows; NULLs for unmatched right |
| RIGHT JOIN |
All right rows; NULLs for unmatched left |
| FULL OUTER JOIN |
All rows from both; NULLs where no match |
| CROSS JOIN |
Cartesian product — use rarely and deliberately |
CTEs (WITH clauses)
CTEs replace subqueries and make complex logic readable:
WITH monthly_revenue AS (
SELECT
DATE_TRUNC('month', created_at) AS month,
SUM(amount) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY 1
)
SELECT
month,
revenue,
revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change
FROM monthly_revenue
ORDER BY month;
Use CTEs whenever a query has more than two joins or needs a derived table more than once.
Window functions
Window functions compute a value across a set of rows related to the current row — without collapsing them into groups.
-- Rank products by revenue within each category
SELECT
category,
product_name,
revenue,
RANK() OVER (
PARTITION BY category
ORDER BY revenue DESC
) AS rank_in_category
FROM products;
-- Running total
SELECT
order_date,
amount,
SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;
-- Previous row value
SELECT
day,
signups,
signups - LAG(signups, 1, 0) OVER (ORDER BY day) AS daily_delta
FROM signups_daily;
| Function |
Use |
| ROW_NUMBER() |
Unique sequential number per partition |
| RANK() |
Rank with gaps on ties |
| DENSE_RANK() |
Rank without gaps on ties |
| LAG(col, n) |
Value from n rows before |
| LEAD(col, n) |
Value from n rows ahead |
| SUM / AVG OVER |
Running or windowed aggregates |
How to pick the right approach
- Single table, filter and sort? → Simple SELECT + WHERE + ORDER BY.
- Multiple tables? → JOIN — INNER if both sides must match, LEFT if one side is optional.
- Aggregating? → GROUP BY + aggregate functions; HAVING to filter on the aggregate.
- Need ranked or running values without collapsing rows? → Window function.
- Query is getting hard to read? → Break it into CTEs.
Reading EXPLAIN ANALYZE
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;
Look for:
- Seq Scan on a large table — usually means a missing index.
- Rows= estimate vs actual rows — large gaps mean stale statistics; run
ANALYZE table_name.
- Nested Loop with large outer rows — may be better as a Hash Join; Postgres should switch automatically if statistics are current.
Common mistakes
Filtering on a function in WHERE. WHERE YEAR(created_at) = 2025 cannot use an index on created_at; rewrite as WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01'.
Using HAVING instead of WHERE to filter rows. HAVING runs after aggregation; filtering non-aggregated columns with WHERE is faster because it reduces the row count before grouping.
Implicit cross joins. FROM a, b WHERE a.id = b.a_id works but hides intent; explicit JOIN is clearer and less error-prone.
Forgetting NULL in comparisons. WHERE col != 'foo' excludes NULLs silently. Use WHERE col != 'foo' OR col IS NULL if you want NULLs included.
What to skip
- **SELECT *** in application code — list columns explicitly; it prevents index-only scans and breaks when the schema changes.
- Correlated subqueries in SELECT for large tables — they execute once per row; rewrite as a JOIN or CTE.
- OR on indexed columns —
WHERE a = 1 OR b = 2 often skips both indexes; use UNION ALL of two queries instead.
FAQ
What is the difference between WHERE and HAVING?
WHERE filters individual rows before grouping; HAVING filters groups after aggregation. You cannot reference aggregate functions in WHERE.
When should I use a subquery vs a JOIN?
Prefer JOINs for readability and optimizer flexibility. Subqueries (or CTEs) are useful when the derived set is referenced more than once or the logic is clearer that way.
How do I deduplicate rows?
Use SELECT DISTINCT for simple cases. For "keep one row per group by some key" use ROW_NUMBER() OVER (PARTITION BY key ORDER BY tiebreaker) = 1 in a CTE.
What is the fastest way to count rows in a large table?
On Postgres, SELECT reltuples FROM pg_class WHERE relname = 'your_table' returns an approximate count instantly. For an exact count you must do a full COUNT(*).
Where to go next