SQL for Web Developers: JOINs, Indexes, CTEs, Window Functions, and N+1 Queries

SQL is the language of relational databases, and despite the rise of ORMs, every web developer benefits from understanding it directly. ORMs help you write queries faster; SQL helps you understand what queries are being run and how to make them fast.

The Core Queries

SELECT

-- All columns
SELECT * FROM users;

-- Specific columns
SELECT id, name, email FROM users;

-- With a condition
SELECT id, name FROM users WHERE active = true;

-- Multiple conditions
SELECT id, name FROM users 
WHERE active = true 
  AND created_at > '2024-01-01';

-- Sorted
SELECT id, name FROM users ORDER BY created_at DESC;

-- Limited
SELECT id, name FROM users ORDER BY created_at DESC LIMIT 20;

-- Offset (page 3 with 20 per page)
SELECT id, name FROM users ORDER BY created_at DESC LIMIT 20 OFFSET 40;

INSERT

-- Single row
INSERT INTO users (name, email, created_at)
VALUES ('Alice', '[e-mail]', NOW());

-- Multiple rows
INSERT INTO users (name, email) VALUES
  ('Alice', '[e-mail]'),
  ('Bob', '[e-mail]'),
  ('Carol', '[e-mail]');

-- Returning the created row (PostgreSQL)
INSERT INTO users (name, email) 
VALUES ('Alice', '[e-mail]')
RETURNING id, created_at;

UPDATE

-- Update specific rows
UPDATE users 
SET active = false, updated_at = NOW()
WHERE last_login < NOW() - INTERVAL '1 year';

-- Always include WHERE (without it, updates every row)
UPDATE users SET active = true;  -- affects all users!

DELETE

DELETE FROM users WHERE id = 123;

-- Soft delete pattern (keep the row, mark it deleted)
UPDATE users SET deleted_at = NOW() WHERE id = 123;

Joins

Joins combine rows from multiple tables based on a relationship.

-- Tables:
-- users: id, name
-- posts: id, user_id, title, published_at

-- INNER JOIN: only rows with a match in both tables
SELECT users.name, posts.title
FROM posts
INNER JOIN users ON posts.user_id = users.id;

-- LEFT JOIN: all posts, with user info if available (user_id might be null)
SELECT posts.title, users.name
FROM posts
LEFT JOIN users ON posts.user_id = users.id;

-- Multiple joins
SELECT 
  posts.title,
  users.name AS author,
  COUNT(comments.id) AS comment_count
FROM posts
INNER JOIN users ON posts.user_id = users.id
LEFT JOIN comments ON comments.post_id = posts.id
GROUP BY posts.id, posts.title, users.name;

Visual guide to JOINs:

• INNER JOIN: rows where both sides match
• LEFT JOIN: all rows from left, match from right (or NULL)
• RIGHT JOIN: all rows from right, match from left (or NULL)
• FULL OUTER JOIN: all rows from both sides

Aggregations

-- Count
SELECT COUNT(*) FROM users WHERE active = true;
SELECT COUNT(DISTINCT country) FROM users;

-- Sum, avg, min, max
SELECT 
  SUM(amount) AS total,
  AVG(amount) AS average,
  MIN(amount) AS minimum,
  MAX(amount) AS maximum
FROM orders
WHERE status = 'completed';

-- GROUP BY
SELECT 
  country,
  COUNT(*) AS user_count,
  AVG(age) AS avg_age
FROM users
GROUP BY country
ORDER BY user_count DESC;

-- HAVING (filter on aggregated results)
SELECT 
  user_id,
  COUNT(*) AS post_count
FROM posts
GROUP BY user_id
HAVING COUNT(*) > 10  -- only users with more than 10 posts
ORDER BY post_count DESC;

Subqueries and CTEs

-- Subquery in WHERE
SELECT name FROM users
WHERE id IN (
  SELECT DISTINCT user_id FROM posts WHERE published_at > NOW() - INTERVAL '30 days'
);

-- CTE (Common Table Expression) — more readable
WITH active_users AS (
  SELECT id, name FROM users WHERE active = true
),
recent_posters AS (
  SELECT DISTINCT user_id FROM posts 
  WHERE published_at > NOW() - INTERVAL '30 days'
)
SELECT active_users.name
FROM active_users
INNER JOIN recent_posters ON active_users.id = recent_posters.user_id;

CTEs make complex queries readable by breaking them into named steps.

Indexes

Indexes dramatically speed up lookups at the cost of slower writes and storage.

-- Create an index on a frequently queried column
CREATE INDEX idx_users_email ON users(email);

-- Composite index (for queries that filter on both columns)
CREATE INDEX idx_posts_user_published ON posts(user_id, published_at);

-- Unique index (also enforces uniqueness)
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

When to index:

• Columns in WHERE clauses
• Foreign key columns (joins use them)
• Columns in ORDER BY when queries also filter

When NOT to index everything:

• Small tables (full scans are fast)
• Columns that are written frequently
• Columns with very low cardinality (e.g., boolean columns — few distinct values mean the index isn't selective)

Check if your index is used:

EXPLAIN SELECT * FROM users WHERE email = '[e-mail]';
-- Look for "Index Scan" vs "Seq Scan"

Common Patterns

Upsert (insert or update)

-- PostgreSQL
INSERT INTO user_settings (user_id, theme, font_size)
VALUES (123, 'dark', 16)
ON CONFLICT (user_id)
DO UPDATE SET theme = EXCLUDED.theme, font_size = EXCLUDED.font_size;

-- MySQL
INSERT INTO user_settings (user_id, theme, font_size)
VALUES (123, 'dark', 16)
ON DUPLICATE KEY UPDATE theme = VALUES(theme), font_size = VALUES(font_size);

Pagination with cursor

-- Offset pagination (breaks when rows are added/deleted between requests)
SELECT * FROM posts ORDER BY id DESC LIMIT 20 OFFSET 40;

-- Cursor pagination (stable)
SELECT * FROM posts 
WHERE id < 12345  -- last_seen_id from previous page
ORDER BY id DESC 
LIMIT 20;

Window Functions

-- Rank users by post count within each country
SELECT 
  name,
  country,
  post_count,
  RANK() OVER (PARTITION BY country ORDER BY post_count DESC) AS rank_in_country
FROM (
  SELECT u.name, u.country, COUNT(p.id) AS post_count
  FROM users u
  LEFT JOIN posts p ON p.user_id = u.id
  GROUP BY u.id, u.name, u.country
) user_posts;

-- Running total
SELECT 
  date,
  revenue,
  SUM(revenue) OVER (ORDER BY date) AS cumulative_revenue
FROM daily_revenue;

Transactions

Group operations so they either all succeed or all fail:

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- If anything fails, rollback everything
COMMIT;  -- or ROLLBACK to undo

In application code:

// Node.js with pg
const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [100, fromId]);
  await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [100, toId]);
  await client.query('COMMIT');
} catch (e) {
  await client.query('ROLLBACK');
  throw e;
} finally {
  client.release();
}

N+1 Queries

The most common ORM performance problem: loading a list then querying each item separately.

// N+1 problem: 1 query for users + N queries for their posts
const users = await User.findAll();
for (const user of users) {
  user.posts = await Post.findAll({ where: { userId: user.id } });
}
// 101 queries for 100 users

// Fix: one query with JOIN
const users = await User.findAll({ include: Post });
// 1 query: SELECT users.*, posts.* FROM users LEFT JOIN posts...

In raw SQL, joins are almost always more efficient than separate queries in a loop.

SQL rewards understanding over abstraction. When an ORM query is slow, EXPLAIN ANALYZE shows you exactly what's happening and why.