SQL Every Developer Should Know

You might use an ORM. You should still know SQL.

ORMs are great for routine operations — basic CRUD, simple queries, relationship loading. But the moment you need a report, debug a performance issue, or figure out why the ORM generated a terrible query, you need SQL. And "I'll learn it when I need it" means learning it under pressure when something's broken.

SQL is one of the most stable technologies in software. The core syntax hasn't changed fundamentally since the 1980s. What you learn today will work for the rest of your career. There's no framework churn, no version migration, no new paradigm every three years.

The Foundation: SELECT

Every SQL query retrieves data with SELECT. Even if you never write anything else, knowing how to read data from a database makes you more effective.

-- Get everything from a table
SELECT * FROM customers;

-- Get specific columns
SELECT name, email FROM customers;

-- Filter rows
SELECT name, email FROM customers
WHERE created_at > '2026-01-01';

-- Sort results
SELECT name, email FROM customers
WHERE created_at > '2026-01-01'
ORDER BY name;

-- Limit results
SELECT name, email FROM customers
ORDER BY created_at DESC
LIMIT 10;

The mental model: SELECT chooses columns, FROM chooses the table, WHERE filters rows, ORDER BY sorts, LIMIT caps the result. Every query follows this structure.

JOINs: Connecting Related Data

Data is split across tables. Customers in one table, orders in another. JOINs connect them.

-- Get customer names with their order totals
SELECT c.name, o.total, o.created_at
FROM customers c
JOIN orders o ON o.customer_id = c.id;

c and o are aliases — shorthand so you don't type the full table name everywhere. ON specifies how the tables relate.

Types of JOINs

INNER JOIN (or just JOIN): Returns only rows where both tables have a match. A customer with no orders won't appear. An order with no customer won't appear.

LEFT JOIN: Returns all rows from the left table, with matches from the right table where they exist. Customers with no orders appear (with NULL order columns). Use this when you want "all customers, with their orders if they have any."

-- All customers, even those without orders
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

RIGHT JOIN: The reverse of LEFT JOIN. Rarely used — you can always rewrite it as a LEFT JOIN by swapping the table order.

FULL OUTER JOIN: All rows from both tables, matched where possible. Rarely needed in application code.

The rule of thumb: if you want all records from one table regardless of matches, use LEFT JOIN. If you only want records that have matches in both tables, use JOIN.

Aggregation: Counting, Summing, Averaging

Aggregation answers questions about groups of data.

-- How many orders does each customer have?
SELECT c.name, COUNT(o.id) as order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;

-- What's the total revenue per month?
SELECT
    DATE_TRUNC('month', created_at) as month,
    SUM(total) as revenue,
    COUNT(*) as order_count,
    AVG(total) as avg_order_value
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

GROUP BY splits rows into groups. Aggregate functions (COUNT, SUM, AVG, MIN, MAX) operate on each group.

HAVING filters groups after aggregation — like WHERE, but for aggregated results.

-- Customers with more than 5 orders
SELECT c.name, COUNT(o.id) as order_count
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.name
HAVING COUNT(o.id) > 5;

Subqueries: Queries Inside Queries

When one query's result feeds into another.

-- Customers who've ordered more than the average order total
SELECT name FROM customers
WHERE id IN (
    SELECT customer_id FROM orders
    WHERE total > (SELECT AVG(total) FROM orders)
);

Subqueries are powerful but can be hard to read. For complex cases, Common Table Expressions (CTEs) are clearer.

CTEs: Readable Complex Queries

CTEs let you name intermediate result sets, making complex queries readable.

-- Same query as above, but readable
WITH high_value_orders AS (
    SELECT customer_id
    FROM orders
    WHERE total > (SELECT AVG(total) FROM orders)
)
SELECT c.name
FROM customers c
WHERE c.id IN (SELECT customer_id FROM high_value_orders);

CTEs are your best friend for complex reporting queries. Each CTE is a named step, and the final SELECT combines them. You can chain CTEs — each one building on the previous.

Write Operations

INSERT

INSERT INTO customers (name, email) VALUES ('Jane', 'jane@example.com');

UPDATE

-- ALWAYS include WHERE — without it, you update every row
UPDATE customers SET email = 'new@example.com' WHERE id = 42;

The scariest SQL mistake: UPDATE customers SET email = 'new@example.com' without a WHERE clause. Every customer now has the same email. Always write the WHERE clause first.

DELETE

-- ALWAYS include WHERE — same warning as UPDATE
DELETE FROM orders WHERE id = 99;

Same rule: WHERE first. DELETE FROM orders without WHERE empties the entire table.

Pro tip: Before running UPDATE or DELETE, write a SELECT with the same WHERE clause first. Verify that the rows you're about to modify are the ones you intend.

Indexes: Why Your Query Is Slow

An index is a data structure that makes lookups fast — like an index in a book. Without an index, the database reads every row in the table (full table scan) to find matches. With an index, it jumps directly to the relevant rows.

-- Create an index on the column you filter by
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

When to add indexes:

• Columns in WHERE clauses that filter frequently

• Columns in JOIN conditions

• Columns in ORDER BY on large tables

When not to add indexes:

• Small tables (full scan is fast enough)

• Columns that are rarely queried

• Tables with heavy write traffic (indexes slow down inserts and updates)

If a query is slow, the first thing to check is whether the columns in your WHERE and JOIN conditions have indexes.

Common Mistakes

Forgetting WHERE on UPDATE/DELETE. Mentioned twice because it deserves it. This has destroyed production data more times than anyone wants to admit.

SELECT * in application code. In ad hoc queries, SELECT * is fine. In application code, select only the columns you need. It reduces data transfer, prevents issues when columns are added, and makes the query's intent clear.

Not understanding NULL. NULL is not zero. NULL is not an empty string. NULL is "unknown." WHERE status != 'active' does NOT return rows where status is NULL. You need WHERE status != 'active' OR status IS NULL.

String concatenation in queries. Never build queries by concatenating user input into SQL strings. Use parameterized queries. Always. This prevents SQL injection — the most common and most preventable database security vulnerability.

Key Takeaway

Every developer should be comfortable with SELECT (with WHERE, ORDER BY, LIMIT), JOINs (INNER and LEFT), aggregation (GROUP BY with COUNT, SUM, AVG), CTEs for readable complex queries, and basic write operations with careful WHERE clauses. Know that indexes make queries fast and that NULL behaves differently than you expect. And always, always use parameterized queries for user input.