SQL Queries Every Developer Should Actually Know

Most SQL tutorials cover SELECT, WHERE, and simple JOINs. That gets you 60% of the way. Here's the 40% that makes the difference between querying data and actually understanding it.

JOINs: All Four Types and When Each Applies

• INNER JOIN: Returns rows where the join condition matches in both tables. Most common. SELECT u.name, o.total FROM users u INNER JOIN orders o ON u.id = o.user_id — returns only users who have at least one order.
• LEFT JOIN: All rows from the left table, matching rows from the 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.name — includes users with zero orders.
• RIGHT JOIN: All rows from the right table. Rarely needed — you can always restructure to use LEFT JOIN by swapping table order.
• FULL OUTER JOIN: All rows from both tables, NULLs where no match. Use for finding discrepancies between two datasets.

Aggregation Patterns That Actually Come Up

-- Top 3 orders per customer by total
SELECT *
FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY total DESC) as rn
  FROM orders
) ranked
WHERE rn <= 3;

-- Running revenue total by date
SELECT
  date,
  daily_revenue,
  SUM(daily_revenue) OVER (ORDER BY date) as running_total
FROM daily_sales;

-- Email addresses that appear more than once
SELECT email, COUNT(*) as occurrences
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

CTEs: Making Complex Queries Readable

Common Table Expressions (WITH clauses) let you name intermediate results. Instead of nesting subqueries 4 levels deep, CTEs read like sequential steps:

WITH active_users AS (
  SELECT id, name FROM users WHERE status = 'active'
),
recent_orders AS (
  SELECT user_id, SUM(total) as total_spend
  FROM orders
  WHERE created_at > NOW() - INTERVAL '30 days'
  GROUP BY user_id
)
SELECT u.name, COALESCE(o.total_spend, 0) as spend_last_30
FROM active_users u
LEFT JOIN recent_orders o ON u.id = o.user_id
ORDER BY spend_last_30 DESC;

When to Stop Using SQL (And Use Application Code)

Complex string manipulation: SQL string functions are clunky. Extract the data in SQL, manipulate strings in application code.

Business logic with many conditionals: A 200-line SQL query with nested CASE WHEN statements is hard to test and maintain. Move logic that changes frequently to application code.

Recursive operations: SQL has recursive CTEs, but recursive algorithms are generally clearer in application code.