SQL Basics Every Developer Should Know (2026)

You don't need to be a DBA, but you do need to write queries that don't suck.

Why SQL Still Matters in 2026

ORMs are great for CRUD.
But when things get complex:
→ ORM generates terrible queries
→ You need joins, aggregations, window functions
→ Performance debugging requires reading actual SQL
→ The database is usually your bottleneck

Learn SQL once, use it everywhere.
Every "NoSQL" database eventually adds SQL-like querying.

The Essential Queries

SELECT: Read Data

-- Basic
SELECT * FROM users;

-- Specific columns (always prefer this over *)
SELECT id, email, name FROM users;

-- With alias
SELECT 
  u.id AS user_id,
  u.email,
  COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

WHERE: Filter Data

-- Basic conditions
SELECT * FROM users WHERE active = true;
SELECT * FROM products WHERE price > 100;

-- Multiple conditions
SELECT * FROM orders 
WHERE status = 'completed' 
  AND created_at >= '2026-01-01'
  AND total_amount > 50;

-- Pattern matching
SELECT * FROM users WHERE email LIKE '[e-mail]';
SELECT * FROM users WHERE name LIKE 'J%';       -- Starts with J
SELECT * FROM users WHERE phone LIKE '555-____'; -- Wildcard for digits

-- IN clause (cleaner than multiple OR)
SELECT * FROM users WHERE role IN ('admin', 'moderator');

-- NULL handling (⚠️ NULL != NULL)
SELECT * FROM users WHERE email IS NOT NULL;
SELECT * FROM products WHERE description IS NULL OR description = '';

-- BETWEEN (inclusive)
SELECT * FROM orders WHERE created_at BETWEEN '2026-01-01' AND '2026-03-31';

JOINs: Combine Tables

-- INNER JOIN: Only matching rows
SELECT u.name, o.total_amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN: All from left, matching from right (NULL if no match)
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

-- Multiple JOINs
SELECT 
  u.name,
  p.name AS product_name,
  oi.quantity,
  oi.unit_price
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE u.id = 123;

ORDER BY + LIMIT: Sort & Paginate

-- Basic sorting
SELECT * FROM products ORDER BY price ASC;
SELECT * FROM products ORDER BY created_at DESC;

-- Multi-column sort
SELECT * FROM users ORDER BY last_login DESC, name ASC;

-- Pagination (crucial!)
-- Page 1 (items 1-20):
SELECT * FROM products ORDER BY created_at DESC LIMIT 20 OFFSET 0;

-- Page 2 (items 21-40):
SELECT * FROM products ORDER BY created_at DESC LIMIT 20 OFFSET 20;

-- Page N:
SELECT * FROM products ORDER BY created_at DESC LIMIT 20 OFFSET ((N - 1) * 20);

Aggregations & Grouping

-- Basic aggregates
SELECT 
  COUNT(*) AS total_orders,
  SUM(total_amount) AS revenue,
  AVG(total_amount) AS avg_order_value,
  MIN(total_amount) AS min_order,
  MAX(total_amount) AS max_order
FROM orders 
WHERE status = 'completed';

-- GROUP BY with HAVING
SELECT 
  status,
  COUNT(*) AS count,
  SUM(total_amount) AS revenue
FROM orders
GROUP BY status
HAVING COUNT(*) > 10;  -- Filter AFTER grouping (unlike WHERE)

-- Common pattern: Top N per category
SELECT 
  category,
  product_name,
  sales_count
FROM (
  SELECT 
    c.name AS category,
    p.name AS product_name,
    SUM(oi.quantity) AS sales_count,
    ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY SUM(oi.quantity) DESC) AS rank
  FROM categories c
  JOIN products p ON c.id = p.category_id
  JOIN order_items oi ON p.id = oi.product_id
  GROUP BY c.id, p.id
) ranked
WHERE rank <= 3;  -- Top 3 products per category

INSERT, UPDATE, DELETE

-- Insert single row
INSERT INTO users (email, name, role) VALUES ('[e-mail]', 'Test User', 'user');

-- Insert multiple rows (faster than individual inserts)
INSERT INTO products (name, price, category_id) VALUES
  ('Widget A', 29.99, 1),
  ('Widget B', 49.99, 1),
  ('Gadget C', 99.99, 2);

-- Upsert (insert or update if exists)
INSERT INTO user_settings (user_id, theme, notifications)
VALUES (123, 'dark', true)
ON CONFLICT (user_id) DO UPDATE SET
  theme = EXCLUDED.theme,
  notifications = EXCLUDED.notifications;

-- Update
UPDATE products SET price = 34.99 WHERE id = 42;

-- Conditional update
UPDATE users SET 
  login_count = login_count + 1,
  last_login = NOW()
WHERE id = 123;

-- Delete (use with caution!)
DELETE FROM sessions WHERE expires_at < NOW();

-- Soft delete (preferred in production)
UPDATE users SET deleted_at = NOW(), active = false WHERE id = 123;

Subqueries vs CTEs

-- Subquery (harder to read)
SELECT * FROM products 
WHERE price > (SELECT AVG(price) FROM products);

-- CTE — same thing, much clearer
WITH avg_price AS (
  SELECT AVG(price) AS value FROM products
)
SELECT * FROM products, avg_price
WHERE products.price > avg_price.value;

-- Complex example using CTEs
WITH monthly_revenue AS (
  SELECT 
    DATE_TRUNC('month', created_at) AS month,
    SUM(total_amount) AS revenue
  FROM orders
  WHERE status = 'completed'
  GROUP BY month
),
monthly_target AS (
  SELECT 
    month,
    LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
    revenue - LAG(revenue) OVER (ORDER BY month) AS growth
  FROM monthly_revenue
)
SELECT 
  month::text,
  revenue,
  prev_month_revenue,
  ROUND((growth / prev_month_revenue * 100), 1) || '%' AS growth_pct
FROM monthly_target
ORDER BY month;

Indexing Essentials

-- Create index (basic)
CREATE INDEX idx_users_email ON users(email);

-- Composite index (order matters!)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

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

-- Partial index (index only a subset of rows)
CREATE INDEX idx_active_users ON users(id) WHERE active = true;

-- Check what indexes exist
\di table_name   -- In psql
-- Or:
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'users';

-- When to add an index:
-- → WHERE clause columns used frequently
-- → JOIN columns (foreign keys)
-- → Columns in ORDER BY combined with WHERE
-- → Columns with high cardinality (many unique values)

-- When NOT to add an index:
-- → Small tables (< 100 rows)
-- → Columns rarely queried
-- → Columns with low cardinality (e.g., boolean flags)
-- → Tables that are written much more than read

Performance Anti-Patterns

-- ❌ SELECT * (wastes bandwidth, prevents index-only scans)
SELECT * FROM users WHERE email = 'x';

-- ✅ Select only needed columns
SELECT id, name, email FROM users WHERE email = 'x';

-- ❌ N+1 problem (1 query for list + N queries for each item)
-- First query gets 100 users
-- Then loop: SELECT * FROM orders WHERE user_id = ? (100 more queries!)

-- ✅ Use JOIN instead
SELECT u.*, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

-- ❌ Missing WHERE clause on large tables
SELECT COUNT(*) FROM events; -- Scans millions of rows

-- ✅ Always filter
SELECT COUNT(*) FROM events WHERE created_at >= NOW() - INTERVAL '30 days';

-- ❌ LIKE '%term' (can't use index — leading wildcard)
SELECT * FROM products WHERE name '%widget';

-- ✅ Use full-text search or trigram index instead
SELECT * FROM products WHERE name ILIKE 'widget%'; -- Leading literal can use index

-- ❌ ORDER BY RAND() (slow on large tables)
SELECT * FROM products ORDER BY RANDOM() LIMIT 5;

-- ✅ Alternative approaches
-- For sequential IDs: random ID range then LIMIT
SELECT * FROM products 
WHERE id >= (RANDOM() * (SELECT MAX(id) FROM products))
LIMIT 5;

-- ❌ No LIMIT on unbounded queries
SELECT * FROM logs; -- Returns ALL rows

-- ✅ Always paginate
SELECT * FROM logs ORDER BY created_at DESC LIMIT 100 OFFSET 0;

Transactions

BEGIN;

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

INSERT INTO transactions (from_account, to_account, amount)
VALUES (1, 2, 100);

COMMIT;  -- Both updates apply atomically

-- If something goes wrong:
ROLLBACK;  -- Everything reverts

In Node.js:

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 (err) {
  await client.query('ROLLBACK');
  throw err;
} finally {
  client.release();
}

Quick Reference

Task	Syntax
Get all	SELECT * FROM t
Filter	WHERE col = val
Pattern match	WHERE col LIKE '%term'
Combine tables	FROM a JOIN b ON a.id = b.a_id
Group & aggregate	GROUP BY col HAVING condition
Sort	ORDER BY col DESC
Paginate	LIMIT n OFFSET m
Insert row	INSERT INTO t (cols) VALUES (vals)
Update	UPDATE t SET col = val WHERE cond
Delete	DELETE FROM t WHERE cond
Aggregate functions	COUNT(), SUM(), AVG(), MIN(), MAX()
Combine results	UNION (dedupes) / UNION ALL (keeps all)
Check existence	EXISTS (subquery) or IN (...)
Null coalesce	COALESCE(col, default)

---

What's your SQL level? Comfortable with basics or diving into query optimization?

Follow @armorbreak for more practical developer guides.