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

-- 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; 

-- Single row INSERT INTO users (name, email, created_at) VALUES ('Alice', 'alice@example.com', NOW()); -- Multiple rows INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com'), ('Bob', 'bob@example.com'), ('Carol', 'carol@example.com'); -- Returning the created row (PostgreSQL) INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com') RETURNING id, created_at; 

-- 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 FROM users WHERE id = 123; -- Soft delete pattern (keep the row, mark it deleted) UPDATE users SET deleted_at = NOW() WHERE id = 123; 

-- 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; 

-- 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; 

-- 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; 

-- 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); 

EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com'; -- Look for "Index Scan" vs "Seq Scan" 

-- 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); 

-- 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; 

-- 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; 

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 

// 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 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...