SQL for Developers: The Practical Guide (2026)

SQL is not scary. It's the most valuable tool in your toolkit. Here's what you actually need to know to be productive.

The Essentials: SELECT

-- Basic query
SELECT name, email FROM users;

-- Select all columns (okay for exploration, avoid in production code)
SELECT * FROM users LIMIT 10;

-- Aliases (make output readable)
SELECT 
  u.name AS user_name,
  u.email,
  COUNT(o.id) AS order_count,
  SUM(o.total) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name, u.email
HAVING COUNT(o.id) > 0
ORDER BY total_spent DESC
LIMIT 20;

-- Filtering with WHERE (the most important clause!)
SELECT * FROM products
WHERE price > 50                          -- Comparison
  AND category = 'electronics'            -- Multiple conditions
  AND (status = 'active' OR status = 'new') -- Grouping with OR
  AND created_at >= '2026-01-01'         -- Date comparison
  AND name LIKE '%wireless%'             -- Pattern matching
  AND id IN (1, 2, 3, 4, 5)              -- List match
  AND description IS NOT NULL             -- NULL check
ORDER BY price DESC;

JOINs: Combining Data from Multiple Tables

-- Visual guide to JOIN types:
/*
   Table A (users)          Table B (orders)
   ┌────┬────────┐        ┌────┬─────────┐
   │ id │  name  │        │ id │ user_id │
   ├────┼────────┤        ├────┼─────────┤
   │  1 │ Alice  │        │  1 │    1    │
   │  2 │ Bob    │        │  2 │    1    │
   │  3 │ Carol  │        │  3 │    2    │
   └────┴────────┘        └────┴─────────┘
*/

-- INNER JOIN: Only matching rows (most common)
-- Result: Alice (2 orders), Bob (1 order). Carol excluded (no orders)
SELECT u.name, o.total 
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN: All from left + matches from right (NULL if no match)
-- Result: Alice (2), Bob (1), Carol (NULL)
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- RIGHT JOIN: All from right + matches from left (rarely used)
-- FULL OUTER JOIN: All rows from both sides (PostgreSQL/MySQL support)

-- Self-Join (hierarchical data: manager → employee)
SELECT 
  e.name AS employee,
  m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Multiple JOINs (real-world query):
SELECT 
  p.name AS product_name,
  c.name AS category_name,
  s.name AS supplier_name,
  COALESCE(SUM(i.quantity), 0) AS stock_quantity
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
LEFT JOIN suppliers s ON p.supplier_id = s.id
LEFT JOIN inventory i ON p.id = i.product_id
WHERE p.active = true
GROUP BY p.id, p.name, c.name, s.name;

Aggregation & Grouping

-- Basic aggregation functions:
SELECT 
  COUNT(*) AS total_orders,           -- Total rows
  COUNT(DISTINCT user_id) AS buyers, -- Unique values
  SUM(total) AS revenue,             -- Sum (numbers only)
  AVG(total) AS avg_order_value,     -- Average
  MIN(total) AS smallest_order,      -- Minimum
  MAX(total) AS largest_order,       -- Maximum
  STRING_AGG(name, ', ') AS all_names -- PostgreSQL (list aggregation)
FROM orders
WHERE status = 'completed';

-- GROUP BY: Split into groups and aggregate each
SELECT 
  DATE(created_at) AS order_date,
  COUNT(*) AS orders,
  SUM(total) AS daily_revenue
FROM orders
WHERE created_at >= '2026-06-01'
GROUP BY DATE(created_at)
ORDER BY daily_revenue DESC;

-- HAVING: Filter AFTER grouping (WHERE filters BEFORE grouping)
SELECT 
  user_id,
  COUNT(*) AS order_count,
  SUM(total) AS total_spent
FROM orders
GROUP BY user_id
HAVING SUM(total) > 1000       -- Only big spenders
   AND COUNT(*) >= 5            -- Frequent buyers too
ORDER BY total_spent DESC;

-- Window functions (powerful! No GROUP BY needed):
SELECT 
  name,
  department,
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary,
  LAG(salary) OVER (ORDER BY hire_date) AS prev_salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS global_rank
FROM employees;

Data Modification

-- INSERT: Add new rows
INSERT INTO users (name, email, role, created_at)
VALUES ('Alice', '[e-mail]', 'user', NOW()),
       ('Bob', '[e-mail]', 'user', NOW());

-- INSERT from SELECT (copy data between tables):
INSERT INTO archived_users (user_id, name, email, archived_at)
SELECT id, name, email, NOW()
FROM users
WHERE status = 'inactive'
  AND last_login < NOW() - INTERVAL '1 year';

-- UPDATE: Modify existing rows
UPDATE products
SET price = price * 1.10,          -- 10% price increase
    updated_at = NOW()
WHERE category = 'electronics'
  AND active = true;

-- UPDATE with JOIN (update based on another table's data):
UPDATE orders o
SET status = 'shipped',
    shipped_at = NOW()
FROM shipments s
WHERE o.shipment_id = s.id
  AND s.status = 'delivered';

-- DELETE: Remove rows
DELETE FROM sessions
WHERE expires_at < NOW();

-- DELETE with JOIN (delete based on related data):
DELETE FROM order_items
WHERE order_id IN (
  SELECT id FROM orders WHERE status = 'cancelled' AND created_at < NOW() - INTERVAL '30 days'
);

-- ⚠️ UPSERT: Insert or update if exists (PostgreSQL):
INSERT INTO user_preferences (user_id, theme, language)
VALUES (42, 'dark', 'en')
ON CONFLICT (user_id)                    -- If user_id already exists
DO UPDATE SET theme = EXCLUDED.theme,     -- Update these fields
                language = EXCLUDED.language,
                updated_at = NOW();

-- MySQL equivalent:
INSERT INTO user_preferences (user_id, theme, language)
VALUES (42, 'dark', 'en')
ON DUPLICATE KEY UPDATE theme = VALUES(theme),
                         language = VALUES(language),
                         updated_at = NOW();

Indexing for Performance

-- Before optimizing: Check what's slow!
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;
-- Look for "Seq Scan" (bad) vs "Index Scan" (good)

-- Create index on single column:
CREATE INDEX idx_users_email ON users (email);

-- Composite index (order matters!):
CREATE INDEX idx_orders_user_status ON orders (user_id, status);
-- Useful for: WHERE user_id = ? AND status = ?
-- Also useful for: WHERE user_id = ? (uses first column)
-- NOT useful for: WHERE status = ? alone (can't use second column without first)

-- Partial index (index only a subset of rows):
CREATE INDEX idx_active_products_price ON products (price)
WHERE active = true;
-- Smaller index = faster writes + faster reads for common queries

-- Unique index (enforces uniqueness):
CREATE UNIQUE INDEX users_email_unique ON users (email);

-- Covering index (includes all columns needed by query):
CREATE INDEX idx_orders_covering ON orders (user_id, status, total, created_at);
-- Query can be satisfied entirely from the index (no table lookup needed!)

-- Text search index (PostgreSQL):
CREATE INDEX idx_posts_search ON posts USING gin(to_tsvector('english', content));

-- When to index:
✅ Columns in WHERE clauses frequently
✅ Columns used in JOIN conditions
✅ Columns in ORDER BY / GROUP BY on large tables
❌ Tables with very few rows (< 1000)
❌ Columns with low selectivity (e.g., boolean with 90% same value)
❌ Columns that change frequently (high write cost)

-- Find unused indexes (PostgreSQL):
SELECT * FROM pg_stat_user_indexes WHERE idx_scan < 50 ORDER BY idx_scan ASC;

Common Patterns & Gotchas

-- Pagination (correct way — stable ordering required!):
SELECT * FROM posts
WHERE published = true
ORDER BY created_at DESC, id DESC  -- Must include unique tiebreaker!
LIMIT 20 OFFSET 40;               -- Page 3 (offset = (page-1) * limit)

-- Better pagination for large datasets (cursor-based):
SELECT * FROM posts
WHERE published = true
  AND (created_at, id) < ('2026-06-01 12:00:00', 99999)  -- Cursor from last row of previous page
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- NULL handling (tricky!):
-- NULL != NULL in SQL!
SELECT * FROM users WHERE email = NULL;      -- Returns NOTHING (even null emails)!
SELECT * FROM users WHERE email IS NULL;      -- Correct way to find NULLs
SELECT * FROM users WHERE email IS NOT NULL;  -- Find non-NULLs

-- COALESCE: Return first non-NULL value:
SELECT COALESCE(phone, email, 'N/A') AS contact_method FROM users;

-- NULLIF: Return NULL if two values are equal:
SELECT NULLIF(status, 'active') AS deactivated_at FROM users;

-- Date/time operations (PostgreSQL):
SELECT 
  NOW(),                              -- Current timestamp
  CURRENT_DATE,                       -- Today's date
  created_at,
  NOW() - created_at AS age,          -- Interval since creation
  EXTRACT(EPOCH FROM (NOW() - created_at)) AS age_seconds,
  DATE_TRUNC('hour', created_at) AS hour_bucket,
  created_at + INTERVAL '7 days' AS expires_at
FROM users
WHERE created_at >= NOW() - INTERVAL '30 days';

-- Transaction (atomic operations):
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- Both succeed or neither does
-- On error: ROLLBACK;

-- CTE (Common Table Expression) — readable complex queries:
WITH monthly_revenue AS (
  SELECT DATE_TRUNC('month', created_at) AS month,
         SUM(total) AS revenue
  FROM orders
  WHERE status = 'completed'
  GROUP BY DATE_TRUNC('month', created_at)
),
monthly_avg AS (
  SELECT AVG(revenue) AS avg_revenue FROM monthly_revenue
SELECT mr.month, mr.revenue, ma.avg_revenue,
       CASE WHEN mr.revenue > ma.avg_revenue THEN 'above' ELSE 'below' END AS performance
FROM monthly_revenue mr
CROSS JOIN monthly_avg ma
ORDER BY mr.month;

---

What SQL topic confuses you most? Window functions? JOINs? Performance tuning?

Follow @armorbreak for more practical developer guides.