SQL Fundamentals
SQL is the one programming language that almost every software engineer ends up writing, whether they work on backends, data pipelines, mobile apps with SQLite, or analytics dashboards. It is also the language people most often learn by copy-paste — which is why so many production bugs come from a LEFT JOIN that silently became an INNER JOIN, a NOT IN that returns nothing, or a SUM that is exactly double what it should be.
This lesson builds SQL up from first principles. Every concept is demonstrated on one small schema that you can create in a few seconds, so you can run every query yourself and check the results printed here. The goal is not to memorise syntax (the SQL cheat sheet is for that) but to understand why each query returns what it returns — the mental model that lets you predict a query's output before you run it.
How to use this page
Open psql (or any PostgreSQL client — a free hosted database or docker run -e POSTGRES_PASSWORD=pw postgres both work), paste the schema from The Running Example, and run the queries as you read. Where MySQL or SQLite behave differently, the difference is called out. The sequel, Advanced SQL, uses the same schema.
If you have not yet met relations, tuples, keys and relational algebra, skim The Relational Model first — SQL is a (slightly impure) implementation of those ideas.
What SQL Is
SQL (Structured Query Language) is a declarative language for working with relational data. "Declarative" is the key word: you describe what result you want, and the database decides how to compute it.
Compare two ways of finding the London customers:
# Imperative: you choose the algorithm
result = []
for row in customers: # full scan, in this order
if row.city == "London":
result.append(row.name)
-- Declarative: you describe the result
SELECT name FROM customers WHERE city = 'London';
The Python version commits to an algorithm — scan every row, in order. The SQL version says nothing about scanning. If an index on city exists, the database's query planner will use it; if the table is tiny, it will scan; if the table is partitioned by city, it will skip whole partitions. The same SQL text gets faster as the database gains indexes, statistics and better algorithms, without you changing a character. That separation between the logical question and the physical execution is the core idea of the relational model, and it is why SQL has survived for 50 years.
The Sub-languages of SQL
SQL is usually divided into four groups of statements:
| Group | Stands for | Statements | Purpose |
|---|---|---|---|
| DDL | Data Definition Language | CREATE, ALTER, DROP, TRUNCATE | Define the shape of data: tables, columns, constraints, indexes, views |
| DML | Data Manipulation Language | INSERT, UPDATE, DELETE, MERGE | Change the rows inside tables |
| DQL | Data Query Language | SELECT | Read data (often lumped into DML) |
| DCL | Data Control Language | GRANT, REVOKE | Control who may do what |
| TCL | Transaction Control Language | BEGIN, COMMIT, ROLLBACK, SAVEPOINT | Group statements into atomic units |
SQL Dialects
There is an ISO SQL standard, but every engine implements a superset with its own extensions and omissions. This lesson uses PostgreSQL, which follows the standard closely. The three engines you will meet most often:
| Feature | PostgreSQL | MySQL 8 | SQLite 3 |
|---|---|---|---|
| String concatenation | 'a' || 'b' | CONCAT('a','b') (|| is OR by default) | 'a' || 'b' |
| Auto-increment key | GENERATED ... AS IDENTITY | AUTO_INCREMENT | INTEGER PRIMARY KEY |
RETURNING clause | Yes | No (MariaDB has it) | Yes (3.35+) |
FULL OUTER JOIN | Yes | No | Yes (3.39+) |
INTERSECT / EXCEPT | Yes | Yes (8.0.31+) | Yes |
| Types enforced strictly | Yes | Mostly (depends on sql_mode) | No — "type affinity" unless STRICT tables |
| Foreign keys enforced | Always | InnoDB only | Only after PRAGMA foreign_keys = ON |
The Running Example
A tiny online shop: customers place orders; each order has line items referencing products. There is also an employees table with a self-referencing manager_id for hierarchy examples.
erDiagram
CUSTOMERS ||--o{ ORDERS : places
ORDERS ||--|{ ORDER_ITEMS : contains
PRODUCTS ||--o{ ORDER_ITEMS : "appears in"
EMPLOYEES ||--o{ EMPLOYEES : manages
CUSTOMERS {
int customer_id PK
text name
text email
text city
date signup_date
}
ORDERS {
int order_id PK
int customer_id FK
date order_date
text status
numeric shipping_fee
}
ORDER_ITEMS {
int order_id PK
int product_id PK
int quantity
numeric unit_price
}
PRODUCTS {
int product_id PK
text name
text category
numeric price
int stock
}
EMPLOYEES {
int employee_id PK
text name
int manager_id FK
text department
numeric salary
date hire_date
}
Paste this whole block into PostgreSQL:
DROP TABLE IF EXISTS order_items, orders, products, customers, employees CASCADE;
CREATE TABLE customers (
customer_id integer PRIMARY KEY,
name text NOT NULL,
email text NOT NULL UNIQUE,
city text, -- nullable on purpose
signup_date date NOT NULL DEFAULT CURRENT_DATE
);
CREATE TABLE products (
product_id integer PRIMARY KEY,
name text NOT NULL,
category text NOT NULL,
price numeric(10,2) NOT NULL CHECK (price >= 0),
stock integer NOT NULL DEFAULT 0 CHECK (stock >= 0)
);
CREATE TABLE orders (
order_id integer PRIMARY KEY,
customer_id integer REFERENCES customers (customer_id), -- NULL = guest checkout
order_date date NOT NULL,
status text NOT NULL
CHECK (status IN ('pending', 'shipped', 'delivered', 'cancelled')),
shipping_fee numeric(6,2) NOT NULL DEFAULT 0
);
CREATE TABLE order_items (
order_id integer REFERENCES orders (order_id) ON DELETE CASCADE,
product_id integer REFERENCES products (product_id),
quantity integer NOT NULL CHECK (quantity > 0),
unit_price numeric(10,2) NOT NULL,
PRIMARY KEY (order_id, product_id)
);
CREATE TABLE employees (
employee_id integer PRIMARY KEY,
name text NOT NULL,
manager_id integer REFERENCES employees (employee_id),
department text NOT NULL,
salary numeric(10,2) NOT NULL,
hire_date date NOT NULL
);
INSERT INTO customers VALUES
(1, 'Asha Rao', 'asha@example.com', 'Mumbai', '2026-01-05'),
(2, 'Ben Carter', 'ben@example.com', 'London', '2026-02-10'),
(3, 'Chen Wei', 'chen@example.com', 'Singapore', '2026-02-20'),
(4, 'Diana Ortiz', 'diana@example.com', 'London', '2026-03-15'),
(5, 'Eve Adams', 'eve@example.com', NULL, '2026-04-01');
INSERT INTO products VALUES
(1, 'Laptop', 'Electronics', 1200.00, 10),
(2, 'Headphones', 'Electronics', 150.00, 50),
(3, 'Desk', 'Furniture', 300.00, 8),
(4, 'Chair', 'Furniture', 120.00, 25),
(5, 'Notebook', 'Stationery', 5.00, 500);
INSERT INTO orders VALUES
(101, 1, '2026-03-01', 'delivered', 10.00),
(102, 1, '2026-03-15', 'delivered', 5.00),
(103, 2, '2026-03-20', 'shipped', 10.00),
(104, 3, '2026-04-02', 'delivered', 15.00),
(105, 2, '2026-04-10', 'cancelled', 0.00),
(106, 1, '2026-05-05', 'pending', 5.00),
(107, NULL, '2026-05-06', 'delivered', 10.00); -- guest checkout
INSERT INTO order_items VALUES
(101, 1, 1, 1200.00), (101, 2, 1, 150.00),
(102, 4, 2, 120.00),
(103, 3, 1, 300.00), (103, 4, 1, 120.00),
(104, 2, 2, 150.00),
(105, 1, 1, 1200.00),
(106, 3, 1, 300.00), (106, 2, 1, 150.00),
(107, 4, 4, 120.00);
INSERT INTO employees VALUES
(1, 'Grace', NULL, 'Executive', 250000, '2019-01-10'),
(2, 'Hari', 1, 'Engineering', 180000, '2020-03-01'),
(3, 'Ines', 1, 'Sales', 150000, '2020-06-15'),
(4, 'Jamal', 2, 'Engineering', 130000, '2021-02-01'),
(5, 'Kim', 2, 'Engineering', 130000, '2021-09-12'),
(6, 'Leo', 3, 'Sales', 90000, '2022-01-20'),
(7, 'Maya', 4, 'Engineering', 135000, '2023-05-08');
The data is deliberately small but contains every trap this lesson discusses: a customer with a NULL city (Eve), customers with no orders (Diana, Eve), an order with no customer (guest order 107), a product nobody bought (Notebook), a cancelled order, salary ties (Jamal and Kim), and an employee who out-earns her manager (Maya).
For reference, here are the order totals (sum of quantity * unit_price per order), which several sections rely on:
| order_id | customer | order_date | status | items total |
|---|---|---|---|---|
| 101 | Asha | 2026-03-01 | delivered | 1350.00 |
| 102 | Asha | 2026-03-15 | delivered | 240.00 |
| 103 | Ben | 2026-03-20 | shipped | 420.00 |
| 104 | Chen | 2026-04-02 | delivered | 300.00 |
| 105 | Ben | 2026-04-10 | cancelled | 1200.00 |
| 106 | Asha | 2026-05-05 | pending | 450.00 |
| 107 | (guest) | 2026-05-06 | delivered | 480.00 |
MySQL and SQLite notes for the schema
- MySQL: replace
textused inUNIQUE/PRIMARY KEYcolumns withVARCHAR(255)(MySQL cannot index unboundedTEXTwithout a prefix length).CHECKconstraints are only enforced from 8.0.16.DROP TABLE ... CASCADEis accepted but ignored. - SQLite: run
PRAGMA foreign_keys = ON;first or theREFERENCESclauses are decoration.numeric(10,2)does not enforce two decimal places. SQLite does not support dropping multiple tables in one statement — drop them one by one.
DDL: Defining Structure
CREATE TABLE and Data Types
A table definition is a contract: every row must have these columns, of these types, satisfying these rules. Choosing types well is the cheapest data-quality tool you have.
| Kind | PostgreSQL types | Notes |
|---|---|---|
| Integers | smallint (2 B), integer (4 B), bigint (8 B) | Use bigint for IDs in tables that may exceed 2.1 billion rows |
| Exact decimals | numeric(p, s) | Money goes here. Never use float for currency |
| Floating point | real, double precision | Fast, approximate — 0.1 + 0.2 <> 0.3 |
| Text | text, varchar(n), char(n) | In PostgreSQL, text and varchar perform identically; varchar(n) just adds a length check |
| Boolean | boolean | MySQL aliases it to TINYINT(1) |
| Date/time | date, time, timestamp, timestamptz, interval | Prefer timestamptz for events — it stores an absolute instant |
| Identifiers | uuid | 16 bytes; v7 UUIDs are time-ordered and index-friendly |
| Semi-structured | jsonb, arrays (integer[]) | See Advanced SQL |
Floats and money
SELECT 0.1::float8 + 0.2::float8 = 0.3::float8; returns false. Binary floating point cannot represent 0.1 exactly. Store currency as numeric or as an integer number of minor units (cents), never as float/double.
Constraints: Letting the Database Enforce Rules
Constraints are rules the database checks on every write, from every application, script and admin console. Application-level validation can be bypassed; a constraint cannot.
| Constraint | Example | What it guarantees |
|---|---|---|
NOT NULL | name text NOT NULL | The column always has a value |
UNIQUE | email text UNIQUE | No two rows share the value (multiple NULLs are allowed in most engines) |
PRIMARY KEY | customer_id integer PRIMARY KEY | UNIQUE + NOT NULL; the row's identity |
FOREIGN KEY | REFERENCES customers (customer_id) | The value exists in the parent table (or is NULL) |
CHECK | CHECK (price >= 0) | An arbitrary boolean condition on the row |
DEFAULT | DEFAULT CURRENT_DATE | Value used when the INSERT omits the column (not strictly a constraint) |
Try violating each one — the error messages are the point:
INSERT INTO products VALUES (6, 'Pen', 'Stationery', -1.00, 10);
-- ERROR: new row for relation "products" violates check constraint "products_price_check"
INSERT INTO customers (customer_id, name, email) VALUES (6, 'Faye', 'asha@example.com');
-- ERROR: duplicate key value violates unique constraint "customers_email_key"
INSERT INTO orders VALUES (108, 99, '2026-05-10', 'pending', 0);
-- ERROR: insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
-- DETAIL: Key (customer_id)=(99) is not present in table "customers".
Foreign key actions decide what happens to children when a parent row is deleted or its key changes:
| Action | On DELETE of parent | Typical use |
|---|---|---|
NO ACTION / RESTRICT (default) | Error if children exist | Most relationships — make deletion an explicit decision |
CASCADE | Delete the children too | Pure "part-of" relationships: order_items belong to an order |
SET NULL | Set the child's FK to NULL | Optional relationships: an employee whose manager leaves |
SET DEFAULT | Set the child's FK to its default | Rare |
Our order_items.order_id uses ON DELETE CASCADE because a line item has no meaning without its order. orders.customer_id uses the default, so you cannot delete Asha while her orders exist — which is what an accountant would want.
Name your constraints
CONSTRAINT price_non_negative CHECK (price >= 0) produces error messages your on-call engineer can understand, and makes ALTER TABLE ... DROP CONSTRAINT price_non_negative easy. Auto-generated names differ between engines.
Identity Columns
In real systems you rarely choose IDs by hand. The modern standard syntax:
CREATE TABLE tags (
tag_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
label text NOT NULL UNIQUE
);
INSERT INTO tags (label) VALUES ('sale'), ('new') RETURNING tag_id, label;
| tag_id | label |
|---|---|
| 1 | sale |
| 2 | new |
GENERATED ALWAYS rejects manually supplied values (unless you write OVERRIDING SYSTEM VALUE); GENERATED BY DEFAULT allows them. The older PostgreSQL shorthand is serial/bigserial; MySQL uses AUTO_INCREMENT; SQLite makes any INTEGER PRIMARY KEY an alias for the internal rowid. Our running schema uses plain integer keys only so the inserted data is predictable.
Explicit IDs and sequences
If you insert explicit IDs into a GENERATED BY DEFAULT or serial column, the underlying sequence does not advance. The next default-valued insert will try ID 1 and fail with a duplicate key error. After bulk-loading with explicit IDs, reset the sequence: SELECT setval(pg_get_serial_sequence('tags', 'tag_id'), (SELECT max(tag_id) FROM tags));
ALTER TABLE
Schemas evolve. ALTER TABLE changes a live table:
ALTER TABLE customers ADD COLUMN phone text; -- new nullable column
ALTER TABLE customers ADD COLUMN is_vip boolean NOT NULL DEFAULT false;
ALTER TABLE customers ALTER COLUMN phone TYPE varchar(20);
ALTER TABLE customers RENAME COLUMN phone TO phone_number;
ALTER TABLE customers ADD CONSTRAINT email_lowercase CHECK (email = lower(email));
ALTER TABLE customers DROP COLUMN phone_number;
ALTER TABLE customers DROP COLUMN is_vip; -- back to the original shape
Some of these are instant metadata changes; others rewrite the whole table and hold a heavy lock while doing it. In PostgreSQL 11+, adding a column with a constant default is instant; changing a column's type usually rewrites the table. On a large production table that difference is the difference between a non-event and an outage — see Database Migrations for zero-downtime techniques.
DROP, TRUNCATE and DELETE
Three ways to make data go away, with very different semantics:
| Statement | Removes | Speed | Fires row triggers | Can filter rows | Transactional (PostgreSQL) |
|---|---|---|---|---|---|
DELETE FROM t WHERE ... | Selected rows | Row by row | Yes | Yes | Yes |
TRUNCATE t | All rows, keeps the table | Near-instant | No | No | Yes (MySQL: implicit commit) |
DROP TABLE t | The table itself, its indexes, its data | Near-instant | No | No | Yes (MySQL: implicit commit) |
DML: Changing Data
INSERT
-- Name the columns. Positional VALUES break the day someone adds a column.
INSERT INTO customers (customer_id, name, email, city)
VALUES (6, 'Faye Lin', 'faye@example.com', 'Mumbai');
-- Multi-row insert: one round trip, one statement
INSERT INTO products (product_id, name, category, price, stock)
VALUES (6, 'Pen', 'Stationery', 2.50, 1000),
(7, 'Lamp', 'Furniture', 45.00, 30);
-- INSERT ... SELECT: copy query results into a table
CREATE TABLE london_customers (LIKE customers);
INSERT INTO london_customers
SELECT * FROM customers WHERE city = 'London';
signup_date was omitted in the first statement, so it received its DEFAULT CURRENT_DATE.
UPDATE and DELETE
UPDATE products
SET price = price * 1.10, stock = stock - 1
WHERE category = 'Furniture';
DELETE FROM customers WHERE customer_id = 6;
The WHERE clause is what separates a routine update from a career-defining incident. Without it, every row is updated or deleted.
The safe-edit habit
Before running an UPDATE or DELETE by hand, run the same WHERE clause as a SELECT count(*). Then run the write inside BEGIN; ... and check the reported row count before you COMMIT. If it says UPDATE 50000 when you expected 3, ROLLBACK costs nothing.
Updating with data from another table is where dialects diverge:
-- PostgreSQL: UPDATE ... FROM
UPDATE orders o
SET shipping_fee = 0
FROM customers c
WHERE o.customer_id = c.customer_id
AND c.city = 'London';
-- MySQL: UPDATE ... JOIN
UPDATE orders o
JOIN customers c ON o.customer_id = c.customer_id
SET o.shipping_fee = 0
WHERE c.city = 'London';
RETURNING: Get Back What You Changed
PostgreSQL (and SQLite 3.35+) let any INSERT, UPDATE or DELETE return the affected rows. This eliminates a second round trip and, crucially, a race: you see exactly the rows your statement touched, including generated IDs and defaults.
Run this inside a transaction so the shared dataset stays pristine:
BEGIN;
UPDATE products
SET price = price * 1.10
WHERE category = 'Furniture'
RETURNING product_id, name, price;
ROLLBACK;
| product_id | name | price |
|---|---|---|
| 3 | Desk | 330.00 |
| 4 | Chair | 132.00 |
DELETE ... RETURNING * is a handy way to move rows: WITH moved AS (DELETE FROM orders WHERE status = 'cancelled' RETURNING *) INSERT INTO orders_archive SELECT * FROM moved; — a single atomic statement.
MySQL has no RETURNING; use LAST_INSERT_ID() after an insert, or re-select. SQL Server's equivalent is OUTPUT.
Before moving on
If you ran the INSERT, UPDATE and DELETE examples above outside a transaction, re-run the schema block from The Running Example so your results match the tables below.
SELECT and the Logical Order of Evaluation
The single most useful mental model in SQL: a SELECT statement is not evaluated in the order it is written. You write SELECT ... FROM ... WHERE ..., but the database logically processes it like this:
flowchart LR
A["1. FROM / JOIN<br/>build the working row set"] --> B["2. WHERE<br/>filter individual rows"]
B --> C["3. GROUP BY<br/>collapse rows into groups"]
C --> D["4. HAVING<br/>filter groups"]
D --> E["5. SELECT<br/>compute output columns"]
E --> F["6. DISTINCT<br/>remove duplicate rows"]
F --> G["7. ORDER BY<br/>sort"]
G --> H["8. LIMIT / OFFSET<br/>cut the result"]
Window functions (covered in Advanced SQL) are computed between steps 4 and 5 — after grouping, before DISTINCT.
This is the logical order — it defines what the result must be. The physical plan can do things in any order that produces the same result (for example, pushing a WHERE filter down into an index scan, or stopping early once LIMIT is satisfied). But for predicting behaviour, the logical order explains almost every "why doesn't this work?" question:
| Symptom | Explanation from the logical order |
|---|---|
WHERE total > 100 fails when total is a SELECT alias | WHERE (step 2) runs before SELECT (step 5) creates the alias |
ORDER BY total does work with the alias | ORDER BY (step 7) runs after SELECT |
WHERE COUNT(*) > 1 is an error | Aggregates do not exist until GROUP BY (step 3); filter groups with HAVING |
SELECT name, COUNT(*) ... GROUP BY city is an error | After grouping, each output row is a group; name has many values per group |
LIMIT 10 before ORDER BY "returns random rows" | Without ORDER BY, row order is undefined; LIMIT just cuts whatever arrives |
Let us see it work on one query:
SELECT c.city, COUNT(*) AS orders
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status <> 'cancelled'
GROUP BY c.city
HAVING COUNT(*) >= 1
ORDER BY orders DESC, c.city;
- FROM/JOIN pairs each order with its customer: 6 rows (guest order 107 has no match).
- WHERE drops order 105 (cancelled): 5 rows.
- GROUP BY city makes groups: Mumbai {101, 102, 106}, London {103}, Singapore {104}.
- HAVING keeps groups with at least one row: all three.
- SELECT computes
cityandCOUNT(*)per group. - ORDER BY sorts by count descending, then city.
| city | orders |
|---|---|
| Mumbai | 3 |
| London | 1 |
| Singapore | 1 |
Dialect note on aliases
PostgreSQL and MySQL let you reference a SELECT alias in GROUP BY (GROUP BY city_upper) as an extension, and MySQL even allows aliases in HAVING. The standard does not, and SQL Server rejects both. Writing the full expression is always portable.
Filtering and NULL Semantics
WHERE Basics
SELECT name, price FROM products
WHERE category = 'Electronics' AND price < 500; -- Headphones
SELECT name FROM products WHERE price BETWEEN 100 AND 300; -- inclusive: Headphones, Desk, Chair
SELECT name FROM products WHERE category IN ('Furniture', 'Stationery');
SELECT name FROM customers WHERE email LIKE '%@example.com'; -- % = any run, _ = one char
SELECT name FROM customers WHERE name ILIKE 'a%'; -- case-insensitive (PostgreSQL only)
Operator precedence matters: AND binds tighter than OR. WHERE category = 'Furniture' OR category = 'Electronics' AND price > 1000 means Furniture OR (Electronics AND price > 1000) — returning Desk, Chair and Laptop. When mixing AND and OR, always use parentheses.
NULL Means "Unknown"
NULL is not a value; it is a marker meaning "unknown" or "not applicable". The consequence that surprises everyone: any comparison with NULL yields UNKNOWN, not TRUE or FALSE.
SELECT name, city FROM customers WHERE city <> 'London';
| name | city |
|---|---|
| Asha Rao | Mumbai |
| Chen Wei | Singapore |
Eve is missing. Her city is unknown, so "is Eve's city not London?" is answered "unknown", and WHERE keeps only rows where the condition is TRUE. Likewise WHERE city = NULL returns zero rows, always — even for Eve — because NULL = NULL is also UNKNOWN.
The correct predicates are IS NULL and IS NOT NULL:
SELECT name FROM customers WHERE city IS NULL; -- Eve Adams
SELECT name FROM customers WHERE city <> 'London' OR city IS NULL; -- Asha, Chen, Eve
SELECT name FROM customers WHERE city IS DISTINCT FROM 'London'; -- same, NULL-safe
IS DISTINCT FROM is the NULL-safe inequality (standard SQL, supported by PostgreSQL and SQLite 3.39+; MySQL spells NULL-safe equality <=>).
Three-Valued Logic
Because comparisons can be UNKNOWN, boolean logic in SQL has three values. The truth tables follow from "UNKNOWN might be true or false":
| a | b | a AND b | a OR b | NOT a |
|---|---|---|---|---|
| TRUE | UNKNOWN | UNKNOWN | TRUE | FALSE |
| FALSE | UNKNOWN | FALSE | UNKNOWN | TRUE |
| UNKNOWN | UNKNOWN | UNKNOWN | UNKNOWN | UNKNOWN |
The reasoning: FALSE AND anything is FALSE regardless of the unknown; TRUE OR anything is TRUE. Everything else depends on the unknown, so stays UNKNOWN. And NOT UNKNOWN is still UNKNOWN — which is why negating a condition does not give you "all the other rows".
flowchart TD
R["Row arrives at WHERE"] --> E{"Evaluate predicate"}
E -->|"TRUE"| K["Row kept"]
E -->|"FALSE"| D["Row discarded"]
E -->|"UNKNOWN (NULL involved)"| D
WHERE and HAVING and join ON conditions all treat UNKNOWN like FALSE. CHECK constraints are the exception: a CHECK passes when the condition is TRUE or UNKNOWN, so CHECK (price >= 0) accepts a NULL price. That is why NOT NULL is a separate constraint.
Arithmetic, Aggregates and NULL
- Arithmetic with
NULLgivesNULL:5 + NULLisNULL, and'Hi ' || NULLisNULLin PostgreSQL. - Aggregates skip
NULLs:SUM,AVG,MIN,MAX,COUNT(col)ignore them (more in Aggregation). GROUP BYandDISTINCTtreat allNULLs as one group — the one place SQL considersNULLs "equal".
COALESCE, NULLIF and CASE
SELECT name,
COALESCE(city, 'Unknown') AS city, -- first non-NULL argument
CASE WHEN city IS NULL THEN 'needs profile'
WHEN city = 'London' THEN 'UK team'
ELSE 'APAC team' END AS owner
FROM customers
ORDER BY customer_id;
| name | city | owner |
|---|---|---|
| Asha Rao | Mumbai | APAC team |
| Ben Carter | London | UK team |
| Chen Wei | Singapore | APAC team |
| Diana Ortiz | London | UK team |
| Eve Adams | Unknown | needs profile |
NULLIF(a, b) returns NULL if a = b, otherwise a. Its classic use is avoiding division by zero: revenue / NULLIF(order_count, 0) returns NULL instead of an error.
Sorting and Pagination
ORDER BY
Rows in a table have no inherent order. If you need an order, you must ask for it; "it always came back sorted before" is a coincidence of the current physical layout and will break after a VACUUM, an index change or a parallel plan.
SELECT name, city FROM customers ORDER BY city, name;
| name | city |
|---|---|
| Ben Carter | London |
| Diana Ortiz | London |
| Asha Rao | Mumbai |
| Chen Wei | Singapore |
| Eve Adams | NULL |
Where do NULLs sort? PostgreSQL treats NULL as larger than every value (last in ASC, first in DESC). MySQL and SQLite do the opposite (first in ASC). PostgreSQL and SQLite 3.30+ accept ORDER BY city NULLS FIRST; in MySQL use ORDER BY city IS NULL, city.
LIMIT and OFFSET
-- Page 1: most recent three orders
SELECT order_id, order_date FROM orders
ORDER BY order_date DESC, order_id DESC
LIMIT 3 OFFSET 0;
| order_id | order_date |
|---|---|
| 107 | 2026-05-06 |
| 106 | 2026-05-05 |
| 105 | 2026-04-10 |
OFFSET 3 gives page 2: 104, 103, 102. Two rules:
- Always
ORDER BYa unique combination when paginating. If two rows tie onorder_date, their relative order can change between queries, so a row may appear on two pages or none. Addingorder_idas a tie-breaker makes the order total. OFFSETdoes not skip work.OFFSET 100000makes the database produce and discard 100,000 rows. For deep pagination use keyset (seek) pagination — remember the last row and continue after it:
-- Page 2 using the last row of page 1 (order_date = 2026-04-10, order_id = 105)
SELECT order_id, order_date FROM orders
WHERE (order_date, order_id) < ('2026-04-10', 105)
ORDER BY order_date DESC, order_id DESC
LIMIT 3;
The row-value comparison (a, b) < (x, y) means "a < x, or a = x and b < y". With an index on (order_date, order_id) this is a single index seek no matter how deep the page. The trade-offs are covered in Pagination Patterns.
The standard spelling of LIMIT is FETCH FIRST 3 ROWS ONLY (PostgreSQL, Oracle, SQL Server 2012+ with OFFSET); SQL Server's older form is SELECT TOP 3.
Aggregation
Aggregate functions turn many rows into one value: COUNT, SUM, AVG, MIN, MAX, plus STRING_AGG/ARRAY_AGG (PostgreSQL), GROUP_CONCAT (MySQL, SQLite).
COUNT(*) vs COUNT(column) vs COUNT(DISTINCT column)
These three answer different questions, and confusing them is a common interview trap:
SELECT COUNT(*) AS all_rows,
COUNT(customer_id) AS with_customer,
COUNT(DISTINCT customer_id) AS distinct_customers
FROM orders;
| all_rows | with_customer | distinct_customers |
|---|---|---|
| 7 | 6 | 3 |
COUNT(*)counts rows, period.COUNT(customer_id)counts rows wherecustomer_id IS NOT NULL— the guest order is skipped.COUNT(DISTINCT customer_id)counts distinct non-NULL values: {1, 2, 3}.
Similarly AVG(col) divides by the number of non-NULL values, not the number of rows. If a NULL should mean zero, say so: AVG(COALESCE(col, 0)).
An aggregate over zero rows returns one row: COUNT gives 0, but SUM, AVG, MIN and MAX give NULL. SELECT SUM(price) FROM products WHERE category = 'Toys' returns NULL, not 0 — wrap it in COALESCE(..., 0) when the caller expects a number.
GROUP BY
GROUP BY partitions rows into groups that share the same values in the grouping columns, then each aggregate is computed per group. Revenue per category, excluding cancelled orders:
SELECT p.category,
SUM(oi.quantity * oi.unit_price) AS revenue,
SUM(oi.quantity) AS units
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.status <> 'cancelled'
GROUP BY p.category
ORDER BY revenue DESC;
| category | revenue | units |
|---|---|---|
| Electronics | 1800.00 | 5 |
| Furniture | 1440.00 | 9 |
Stationery is absent: no Notebook was sold, so no rows reached the grouping step. (To include zero-revenue categories, start from products and LEFT JOIN — see joins.)
The grouping rule: every column in the SELECT list must be either in GROUP BY or inside an aggregate. PostgreSQL relaxes this slightly — if you group by a table's primary key, you may select any other column of that table, since it is functionally determined. MySQL with ONLY_FULL_GROUP_BY disabled (the pre-5.7 default) lets you select anything and picks an arbitrary value — a silent bug factory. SQLite also allows it.
HAVING vs WHERE
WHERE filters rows before grouping; HAVING filters groups after aggregation. Orders whose items total more than 400:
SELECT order_id, SUM(quantity * unit_price) AS total
FROM order_items
GROUP BY order_id
HAVING SUM(quantity * unit_price) > 400
ORDER BY total DESC;
| order_id | total |
|---|---|
| 101 | 1350.00 |
| 105 | 1200.00 |
| 107 | 480.00 |
| 106 | 450.00 |
| 103 | 420.00 |
A condition that does not involve an aggregate (WHERE status <> 'cancelled') belongs in WHERE: it removes rows early, so less data is grouped. Putting it in HAVING works only if the column is a grouping column, and it is slower.
Grouping by Expressions
You can group by any expression — for example, by month:
SELECT date_trunc('month', order_date)::date AS month, -- MySQL: DATE_FORMAT(order_date, '%Y-%m-01')
COUNT(*) AS orders
FROM orders
GROUP BY 1 -- positional reference to the first SELECT column
ORDER BY 1;
| month | orders |
|---|---|
| 2026-03-01 | 3 |
| 2026-04-01 | 2 |
| 2026-05-01 | 2 |
ROLLUP, CUBE and GROUPING SETS produce subtotals and grand totals in one query; see the advanced cheat sheet.
Joins
A join combines rows from two tables. Conceptually, every join starts as a Cartesian product (every row of A paired with every row of B), then keeps the pairs satisfying the ON condition, and — for outer joins — adds back unmatched rows padded with NULLs.
Join Types at a Glance
Think of the set of rows in A and in B, where the overlap is "rows that have a match":
INNER JOIN LEFT JOIN RIGHT JOIN FULL OUTER JOIN
┌───┐ ┌───┐ ┌───┐ ┌───┐ ┌───┐ ┌───┐ ┌───┐ ┌───┐
│ A ┌─┼─┐ B│ │███┌─┼─┐ B│ │ A ┌─┼─┐██│ │███┌─┼─┐██│
│ │█│█│ │ │███│█│█│ │ │ │█│█│██│ │███│█│█│██│
│ └─┼─┘ │ │███└─┼─┘ │ │ └─┼─┘██│ │███└─┼─┘██│
└───┘ └───┘ └───┘ └───┘ └───┘ └───┘ └───┘ └───┘
matched pairs only all of A + matches all of B + matches everything from both
Venn diagrams are a useful first approximation, but joins operate on rows, not sets — a single row in A can match many rows in B and appear many times in the output. Keep that in mind; it is the root of the row-multiplication pitfall below.
| Join | Keeps | Unmatched rows from A | Unmatched rows from B |
|---|---|---|---|
INNER JOIN | Pairs satisfying ON | Dropped | Dropped |
LEFT [OUTER] JOIN | Inner result + unmatched A | Kept, B columns NULL | Dropped |
RIGHT [OUTER] JOIN | Inner result + unmatched B | Dropped | Kept, A columns NULL |
FULL [OUTER] JOIN | Inner result + unmatched A + unmatched B | Kept | Kept |
CROSS JOIN | Every pair (no ON) | n/a | n/a |
INNER JOIN
SELECT c.name, o.order_id, o.status
FROM customers c
INNER JOIN orders o ON o.customer_id = c.customer_id
ORDER BY o.order_id;
| name | order_id | status |
|---|---|---|
| Asha Rao | 101 | delivered |
| Asha Rao | 102 | delivered |
| Ben Carter | 103 | shipped |
| Chen Wei | 104 | delivered |
| Ben Carter | 105 | cancelled |
| Asha Rao | 106 | pending |
Six rows. Diana and Eve (no orders) vanish, and so does guest order 107 (its customer_id is NULL, and NULL = anything is UNKNOWN). JOIN alone means INNER JOIN.
Row-level view of what happened:
customers orders result
id name id customer_id
1 Asha ──────────────── 101 1 Asha 101
──────────────── 102 1 Asha 102
──────────────── 106 1 Asha 106
2 Ben ──────────────── 103 2 Ben 103
──────────────── 105 2 Ben 105
3 Chen ──────────────── 104 3 Chen 104
4 Diana (no match) -
5 Eve (no match) -
107 NULL (no match) -
LEFT OUTER JOIN
LEFT JOIN keeps every row from the left table. Where there is no match, the right side's columns are NULL.
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;
| name | order_id |
|---|---|
| Asha Rao | 101 |
| Asha Rao | 102 |
| Asha Rao | 106 |
| Ben Carter | 103 |
| Ben Carter | 105 |
| Chen Wei | 104 |
| Diana Ortiz | NULL |
| Eve Adams | NULL |
This enables the anti-join pattern — "rows in A with no match in B":
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL; -- test a column that is NOT NULL in a real match
| name |
|---|
| Diana Ortiz |
| Eve Adams |
Test a column that can never be NULL in a genuine match (a primary key or the join column). NOT EXISTS (see subqueries) expresses the same thing more directly and is often clearer.
RIGHT and FULL OUTER JOIN
RIGHT JOIN is a LEFT JOIN with the tables swapped; most teams standardise on LEFT JOIN for readability. FULL OUTER JOIN keeps unmatched rows from both sides:
SELECT c.name, o.order_id
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.customer_id
ORDER BY o.order_id NULLS LAST, c.name;
| name | order_id |
|---|---|
| Asha Rao | 101 |
| Asha Rao | 102 |
| Ben Carter | 103 |
| Chen Wei | 104 |
| Ben Carter | 105 |
| Asha Rao | 106 |
| NULL | 107 |
| Diana Ortiz | NULL |
| Eve Adams | NULL |
Nine rows: six matched pairs, two customers with no orders, one order with no customer. FULL JOIN is the tool for reconciliation — comparing two sources and finding rows missing from either side. MySQL lacks it; emulate with LEFT JOIN ... UNION ... RIGHT JOIN.
Pitfall 1: Filtering in ON vs WHERE for Outer Joins
This is the most common outer-join bug. Goal: list every customer with their delivered orders, keeping customers who have none.
-- WRONG: the WHERE filter discards the NULL-padded rows
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'delivered'
ORDER BY c.customer_id, o.order_id;
| name | order_id |
|---|---|
| Asha Rao | 101 |
| Asha Rao | 102 |
| Chen Wei | 104 |
Ben, Diana and Eve disappeared. Follow the logical order: the LEFT JOIN produced Diana and Eve with NULL order columns; then WHERE o.status = 'delivered' evaluated NULL = 'delivered' → UNKNOWN → row discarded. The LEFT JOIN has silently become an INNER JOIN.
-- RIGHT: put conditions on the optional table in ON
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.customer_id
AND o.status = 'delivered'
ORDER BY c.customer_id, o.order_id;
| name | order_id |
|---|---|
| Asha Rao | 101 |
| Asha Rao | 102 |
| Ben Carter | NULL |
| Chen Wei | 104 |
| Diana Ortiz | NULL |
| Eve Adams | NULL |
The rule: in a LEFT JOIN, conditions on the right-hand table go in ON; conditions on the left-hand table go in WHERE. ON decides which rows match; WHERE decides which rows survive. For inner joins the two placements are equivalent.
Counting with this pattern — delivered orders per customer, including zeros:
SELECT c.name, COUNT(o.order_id) AS delivered_orders -- COUNT(col), not COUNT(*)
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'delivered'
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;
| name | delivered_orders |
|---|---|
| Asha Rao | 2 |
| Ben Carter | 0 |
| Chen Wei | 1 |
| Diana Ortiz | 0 |
| Eve Adams | 0 |
COUNT(*) would report 1 for Ben, Diana and Eve, because the NULL-padded row is still a row.
Pitfall 2: Row Multiplication (Fan-out)
When you join a table to a child table, each parent row is repeated once per child. Aggregating a parent-level column after such a join double-counts.
Goal: total shipping fees paid by each customer.
-- WRONG: joined order_items before summing an orders-level column
SELECT c.name, SUM(o.shipping_fee) AS shipping
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY c.name
ORDER BY c.name;
| name | shipping |
|---|---|
| Asha Rao | 35.00 |
| Ben Carter | 20.00 |
| Chen Wei | 15.00 |
Asha actually paid 10 + 5 + 5 = 20, not 35. Order 101 has two items, so its 10.00 fee appeared twice; order 106 also has two items. Ben's 10.00 for order 103 was counted twice too (correct: 10 + 0 = 10).
orders.101 (fee 10) ─┬─ item Laptop → row with fee 10
└─ item Headphones → row with fee 10 (duplicate!)
Fixes, in order of preference:
- Don't join tables you don't need. The query above never used
order_items. - Aggregate before joining — reduce the child table to one row per parent in a subquery or CTE, then join:
SELECT c.name,
SUM(o.shipping_fee) AS shipping,
SUM(t.items_total) AS items
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN (SELECT order_id, SUM(quantity * unit_price) AS items_total
FROM order_items GROUP BY order_id) t ON t.order_id = o.order_id
GROUP BY c.name
ORDER BY c.name;
| name | shipping | items |
|---|---|---|
| Asha Rao | 20.00 | 2040.00 |
| Ben Carter | 10.00 | 1620.00 |
| Chen Wei | 15.00 | 300.00 |
COUNT(DISTINCT ...)repairs counts but not sums (two different orders can have the same fee), so it is not a general fix.
The DISTINCT smell
If you find yourself adding DISTINCT to make duplicate rows go away, stop and ask why there are duplicates. It is almost always an unintended fan-out from a join, and DISTINCT hides the symptom while the aggregates stay wrong.
Fan-out compounds: joining a customer to 5 orders and 4 support tickets yields 20 rows per customer, and every SUM over either child is inflated. Each one-to-many branch must be pre-aggregated separately.
CROSS JOIN
A CROSS JOIN returns every combination — the Cartesian product, with no condition. 5 customers × 3 categories = 15 rows:
SELECT c.name, cat.category
FROM customers c
CROSS JOIN (SELECT DISTINCT category FROM products) cat
ORDER BY c.name, cat.category
LIMIT 4;
| name | category |
|---|---|
| Asha Rao | Electronics |
| Asha Rao | Furniture |
| Asha Rao | Stationery |
| Ben Carter | Electronics |
Deliberate uses: building a grid of all (entity, dimension) combinations and then LEFT JOINing facts onto it, so that missing combinations show up as zeros. Accidental cross joins — forgetting the join condition in old-style FROM a, b WHERE ... syntax — are a classic way to produce a billion-row result.
Self-Join
A table can be joined to itself using two aliases. The employees table references itself through manager_id:
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.employee_id = e.manager_id
ORDER BY e.employee_id;
| employee | manager |
|---|---|
| Grace | NULL |
| Hari | Grace |
| Ines | Grace |
| Jamal | Hari |
| Kim | Hari |
| Leo | Ines |
| Maya | Jamal |
LEFT JOIN keeps Grace, who has no manager. The classic interview question — employees who earn more than their manager:
SELECT e.name, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON m.employee_id = e.manager_id
WHERE e.salary > m.salary;
| name | salary | manager | manager_salary |
|---|---|---|---|
| Maya | 135000.00 | Jamal | 130000.00 |
A self-join walks exactly one level of a hierarchy. Walking an arbitrary number of levels (the whole reporting chain) requires a recursive CTE — see Advanced SQL.
Joining Multiple Tables and USING
Joins chain left to right. A query joining customers → orders → order_items → products produces one row per order line:
SELECT c.name, o.order_id, p.name AS product, oi.quantity
FROM customers c
JOIN orders o USING (customer_id) -- USING: same-named columns, appears once in output
JOIN order_items oi USING (order_id)
JOIN products p ON p.product_id = oi.product_id
WHERE c.name = 'Ben Carter'
ORDER BY o.order_id, p.name;
| name | order_id | product | quantity |
|---|---|---|---|
| Ben Carter | 103 | Chair | 1 |
| Ben Carter | 103 | Desk | 1 |
| Ben Carter | 105 | Laptop | 1 |
The last join uses ON only to show both forms side by side; USING (product_id) would work equally well. Avoid NATURAL JOIN, which joins on all same-named columns and silently changes meaning when someone adds a column (here it would also try to match customers.name with products.name).
Subqueries
A subquery is a SELECT nested inside another statement. Where it appears determines what shape it must return.
| Kind | Returns | Appears in | Example |
|---|---|---|---|
| Scalar | One row, one column | Anywhere a value is allowed | WHERE price > (SELECT AVG(price) FROM products) |
| Row / list | One column, many rows | IN, ANY, ALL | WHERE id IN (SELECT ...) |
| Table (derived table) | Any rows and columns | FROM, JOIN | FROM (SELECT ...) t |
| Existence | Anything (only existence matters) | EXISTS | WHERE EXISTS (SELECT 1 ...) |
Scalar Subqueries
Orders whose total exceeds the average order total (634.29):
WITH totals AS ( -- a CTE: a named subquery, covered in Advanced SQL
SELECT order_id, SUM(quantity * unit_price) AS total
FROM order_items GROUP BY order_id
)
SELECT order_id, total
FROM totals
WHERE total > (SELECT AVG(total) FROM totals)
ORDER BY order_id;
| order_id | total |
|---|---|
| 101 | 1350.00 |
| 105 | 1200.00 |
If a scalar subquery returns more than one row, the query fails at runtime ("more than one row returned by a subquery used as an expression"). If it returns zero rows, it yields NULL.
IN and Derived Tables
Customers who bought anything from Electronics:
SELECT name FROM customers
WHERE customer_id IN (
SELECT o.customer_id
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE p.category = 'Electronics'
)
ORDER BY name;
| name |
|---|
| Asha Rao |
| Ben Carter |
| Chen Wei |
Unlike a join, IN cannot multiply rows: Asha bought electronics in two orders but appears once. That makes IN/EXISTS the natural choice for a semi-join ("rows in A that have at least one match in B") where a join would need DISTINCT.
EXISTS and NOT EXISTS
EXISTS (subquery) is true if the subquery returns at least one row. The select list inside is irrelevant — SELECT 1 is the convention.
SELECT p.name
FROM products p
WHERE NOT EXISTS (
SELECT 1 FROM order_items oi WHERE oi.product_id = p.product_id
);
| name |
|---|
| Notebook |
This subquery is correlated: it references p.product_id from the outer query, so logically it is evaluated once per outer row. In practice, modern planners turn EXISTS/NOT EXISTS into a semi-join or anti-join that runs as efficiently as a hash join, so do not fear correlation per se.
Correlated Subqueries
Each customer's most recent order:
SELECT c.name, o.order_id, o.order_date
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_date = (
SELECT MAX(o2.order_date)
FROM orders o2
WHERE o2.customer_id = c.customer_id -- correlation
)
ORDER BY c.name;
| name | order_id | order_date |
|---|---|---|
| Asha Rao | 106 | 2026-05-05 |
| Ben Carter | 105 | 2026-04-10 |
| Chen Wei | 104 | 2026-04-02 |
This works, but if a customer had two orders on the same latest date, both would appear. Window functions (ROW_NUMBER) give precise control over ties — see top-N per group.
The NOT IN and NULL Trap
Find customers who have never ordered. This looks obviously correct:
SELECT name FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);
It returns zero rows. Not Diana, not Eve — nothing.
Why: the subquery returns {1, 1, 2, 3, 2, 1, NULL} — the guest order contributes a NULL. x NOT IN (a, b, NULL) expands to x <> a AND x <> b AND x <> NULL. For Diana (4): TRUE AND TRUE AND UNKNOWN = UNKNOWN. WHERE discards UNKNOWN. A single NULL in the list makes NOT IN unable to ever be TRUE.
flowchart TD
A["4 NOT IN (1, 2, 3, NULL)"] --> B["4 ≠ 1 AND 4 ≠ 2 AND 4 ≠ 3 AND 4 ≠ NULL"]
B --> C["TRUE AND TRUE AND TRUE AND UNKNOWN"]
C --> D["UNKNOWN"]
D --> E["WHERE discards the row"]
Fixes:
-- Preferred: NOT EXISTS is NULL-safe and usually optimised to an anti-join
SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);
-- Or filter NULLs out explicitly
SELECT name FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL);
Both return Diana Ortiz and Eve Adams. Positive IN does not have this problem (4 IN (1, NULL) is UNKNOWN, which is discarded, which is the right answer for "is 4 in the list?"). The trap is specific to negation.
Rule of thumb
Use EXISTS / NOT EXISTS for semi-joins and anti-joins against other tables. Reserve IN / NOT IN for literal lists you control: status IN ('shipped', 'delivered').
ANY and ALL
x > ALL (subquery) is true if x beats every value; x = ANY (subquery) is equivalent to IN. price > ALL (SELECT price FROM products WHERE category = 'Furniture') finds products pricier than every furniture item (Laptop). ALL over an empty set is TRUE, and ALL inherits the same NULL trap as NOT IN. MAX/MIN are usually clearer.
Set Operations
Set operators combine the results of two queries vertically (joins combine horizontally). Both queries must return the same number of columns with compatible types; column names come from the first query.
Two sets to work with — customers who ordered in March and in April:
SELECT customer_id FROM orders WHERE order_date >= '2026-03-01' AND order_date < '2026-04-01'; -- 1, 1, 2
SELECT customer_id FROM orders WHERE order_date >= '2026-04-01' AND order_date < '2026-05-01'; -- 3, 2
| Operation | Meaning | Result |
|---|---|---|
march UNION april | In either, duplicates removed | 1, 2, 3 |
march UNION ALL april | In either, duplicates kept | 1, 1, 2, 3, 2 |
march INTERSECT april | In both | 2 |
march EXCEPT april | In March but not April | 1 |
april EXCEPT march | In April but not March | 3 |
SELECT customer_id FROM orders WHERE order_date >= '2026-03-01' AND order_date < '2026-04-01'
INTERSECT
SELECT customer_id FROM orders WHERE order_date >= '2026-04-01' AND order_date < '2026-05-01';
-- 2 (Ben ordered in both months)
Key points:
UNIONdeduplicates, which costs a sort or hash over the whole result. If duplicates are impossible or acceptable,UNION ALLis strictly faster. Defaulting toUNION ALLand choosingUNIONdeliberately is a good habit.- Set operations treat
NULLs as equal to each other (likeDISTINCT), unlike=. INTERSECT ALL/EXCEPT ALLkeep multiplicities (PostgreSQL supports them; MySQL from 8.0.31; SQLite does not). Oracle spellsEXCEPTasMINUS.ORDER BYandLIMITapply to the combined result; put them once at the end, or parenthesise each branch to sort it individually.
Views
A view is a named, stored query. It holds no data of its own; querying a view runs its definition.
CREATE VIEW order_totals AS
SELECT o.order_id,
o.customer_id,
o.order_date,
o.status,
SUM(oi.quantity * oi.unit_price) AS items_total,
o.shipping_fee
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY o.order_id; -- PK grouping lets us select other orders columns
SELECT customer_id, SUM(items_total + shipping_fee) AS lifetime_value
FROM order_totals
WHERE status <> 'cancelled' AND customer_id IS NOT NULL
GROUP BY customer_id
ORDER BY lifetime_value DESC;
| customer_id | lifetime_value |
|---|---|
| 1 | 2060.00 |
| 2 | 430.00 |
| 3 | 315.00 |
Asha: (1350 + 10) + (240 + 5) + (450 + 5) = 2060.
Why views are useful:
| Benefit | Example |
|---|---|
| Reuse | The "order total" definition lives in one place; nobody re-derives it with the fan-out bug |
| Abstraction | Rename or restructure base tables while keeping the view's interface stable |
| Security | Grant access to a view that exposes only some columns or rows, not the base table |
The planner inlines a simple view's definition into the outer query, so filters on the view are pushed down and a view costs nothing extra. Simple single-table views are even updatable (UPDATE view SET ... writes through to the base table); add WITH CHECK OPTION to stop writes that would fall outside the view's WHERE.
A materialized view stores the result physically: CREATE MATERIALIZED VIEW monthly_revenue AS ...; then REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;. Reads are fast, but data is stale between refreshes. MySQL and SQLite have no materialized views; teams emulate them with summary tables maintained by scheduled jobs or triggers.
Transactions Basics
A transaction groups several statements into one all-or-nothing unit. Placing an order touches three tables — orders, order_items and products (stock) — and must never be half-applied.
BEGIN;
INSERT INTO orders (order_id, customer_id, order_date, status, shipping_fee)
VALUES (108, 4, '2026-05-10', 'pending', 5.00);
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (108, 3, 1, 300.00);
UPDATE products SET stock = stock - 1 WHERE product_id = 3;
COMMIT;
If any statement fails, or the application crashes before COMMIT, none of the changes become visible. Now watch a constraint protect us:
BEGIN;
INSERT INTO orders VALUES (109, 4, '2026-05-11', 'pending', 5.00);
INSERT INTO order_items VALUES (109, 1, 20, 1200.00);
UPDATE products SET stock = stock - 20 WHERE product_id = 1;
-- ERROR: new row for relation "products" violates check constraint "products_stock_check"
COMMIT;
-- ROLLBACK (PostgreSQL: a failed transaction can only roll back)
Only 10 laptops were in stock, so CHECK (stock >= 0) fired. In PostgreSQL, after an error the transaction is "aborted": every further statement is rejected until you end it, and COMMIT is turned into ROLLBACK. Order 109 never existed. (MySQL behaves differently: a failed statement is rolled back on its own, the transaction stays open, and a COMMIT would commit the earlier statements — your application must check every statement's result.)
stateDiagram-v2
[*] --> Active: BEGIN
Active --> Active: statement succeeds
Active --> Committed: COMMIT
Active --> Aborted: statement fails
Active --> RolledBack: ROLLBACK
Aborted --> RolledBack: ROLLBACK or COMMIT
Committed --> [*]
RolledBack --> [*]
Savepoints allow a partial rollback inside a transaction:
BEGIN;
UPDATE customers SET city = 'Pune' WHERE customer_id = 1;
SAVEPOINT before_risky;
UPDATE customers SET email = 'ben@example.com' WHERE customer_id = 1; -- violates UNIQUE
ROLLBACK TO SAVEPOINT before_risky; -- undo only the failed part
COMMIT; -- Asha's city change is kept
(Run UPDATE customers SET city = 'Mumbai' WHERE customer_id = 1; afterwards to restore the example data, and delete order 108 with DELETE FROM orders WHERE order_id = 108; UPDATE products SET stock = stock + 1 WHERE product_id = 3; if you want to match later results exactly.)
Outside an explicit BEGIN, each statement runs in its own transaction (autocommit). The guarantees a transaction provides are summarised as ACID — Atomicity, Consistency, Isolation, Durability. Atomicity is what we just saw. Isolation — what concurrent transactions see of each other — is a deep topic of its own: start with Transactions Basics, then Isolation Levels and Concurrency Control.
War Story (illustrative)
A payments team built a "move money" endpoint as two separate API calls to the database layer: debit account A, then credit account B, each in autocommit mode. Under load, a deploy restarted pods mid-request. Over a weekend, a few hundred debits landed without their matching credits — money that had left one account and arrived nowhere. Reconciliation took days of manual work. The fix was three lines: wrap both statements in BEGIN ... COMMIT. The lesson: any business operation that must be all-or-nothing must be one database transaction, and the transaction boundary belongs in code review checklists.
DCL: GRANT and REVOKE
Databases have their own users and roles, and every object (table, view, schema, function) has privileges: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, EXECUTE, USAGE, and so on.
-- A read-only analyst role
CREATE ROLE analyst NOLOGIN;
GRANT USAGE ON SCHEMA public TO analyst;
GRANT SELECT ON order_totals, products TO analyst; -- the view, not the base tables
-- A login user that inherits it
CREATE ROLE priya LOGIN PASSWORD 'change-me';
GRANT analyst TO priya;
-- The application role can write, but not drop or truncate
CREATE ROLE app_rw NOLOGIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_rw;
-- Take a privilege away
REVOKE DELETE ON customers FROM app_rw;
Principles that matter in practice:
- Least privilege. The application connects as a role that can do exactly what the application needs. Migrations run as a separate, more powerful role. Nobody's application uses the superuser.
- Grant to roles, not people. People join and leave; roles describe jobs.
- Column-level privileges exist (
GRANT SELECT (name, city) ON customers TO support;), and PostgreSQL's row-level security (CREATE POLICY ...) restricts which rows a role sees — the foundation of many multi-tenant designs. GRANT ... ON ALL TABLESaffects only tables that exist now; useALTER DEFAULT PRIVILEGESfor future ones.
MySQL's syntax is similar (GRANT SELECT ON shop.* TO 'priya'@'%';, with users identified by host). SQLite has no users or privileges at all — access control is whatever the filesystem allows.
Putting It Together: Writing a Query Step by Step
Interview and real-world queries are easier if you build them in the logical order rather than starting from SELECT. Task: for each city, the number of distinct customers who placed a non-cancelled order, and their total items revenue, only for cities with revenue above 500.
- FROM / JOIN — which rows do I need? Customers joined to non-cancelled orders and their line items. Does any aggregate span a one-to-many fan-out? Revenue is per line item, so joining items is correct; customer count needs
DISTINCTbecause of fan-out. - WHERE — row filters:
status <> 'cancelled'. - GROUP BY — one output row per city.
- HAVING — revenue > 500.
- SELECT — city, distinct customers, revenue.
- ORDER BY — revenue descending.
SELECT c.city,
COUNT(DISTINCT c.customer_id) AS customers,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status <> 'cancelled'
GROUP BY c.city
HAVING SUM(oi.quantity * oi.unit_price) > 500
ORDER BY revenue DESC;
| city | customers | revenue |
|---|---|---|
| Mumbai | 1 | 2040.00 |
London's revenue is 420 (Ben's order 103) and Singapore's is 300, so both are filtered out by HAVING.
Common Beginner Mistakes
| Mistake | Symptom | Fix |
|---|---|---|
WHERE col = NULL | Always zero rows | WHERE col IS NULL |
NOT IN (subquery) where the subquery can return NULL | Zero rows | NOT EXISTS |
Right-table filter in WHERE after LEFT JOIN | Unmatched rows vanish | Move the filter into ON |
| Summing a parent column after joining children | Totals inflated | Pre-aggregate children, or drop the join |
COUNT(*) after a LEFT JOIN | Zero-match groups report 1 | COUNT(right_table.pk) |
No ORDER BY with LIMIT | Different rows each run | Always order, with a unique tie-breaker |
UNION by default | Unnecessary sort/hash | UNION ALL unless you need dedup |
| Selecting non-grouped columns (MySQL lax mode) | Arbitrary values | Enable ONLY_FULL_GROUP_BY; aggregate or group |
float for money | Cents drift | numeric or integer cents |
UPDATE / DELETE without WHERE | Every row changed | SELECT the WHERE first; run inside BEGIN |
| Building SQL by string concatenation with user input | SQL injection | Parameterised queries / prepared statements, always |
SQL injection
Never concatenate user input into SQL text: "SELECT * FROM customers WHERE email = '" + input + "'" lets an input of ' OR '1'='1 read every row. Use your driver's placeholders ($1, ?, :email), which send the value separately from the query text so it can never be parsed as SQL.
Where to Go Next
- Advanced SQL: Window Functions, CTEs & Query Patterns — the second half of this lesson, on the same schema
- ER Modeling — how a schema like this one is designed in the first place
- Concurrency Control — what happens when two transactions touch the same rows
- SQL Cheat Sheet — syntax at a glance
- Indexing Deep Dive — why some of these queries are fast and others are not
Practice Problems
All problems use the running example schema. Try each one before opening the solution.
Problem 1: Customers in More Than One City?
Which cities have more than one customer? Return the city and the count.
Solution
SELECT city, COUNT(*) AS customers
FROM customers
WHERE city IS NOT NULL
GROUP BY city
HAVING COUNT(*) > 1;
| city | customers |
|---|---|
| London | 2 |
The WHERE city IS NOT NULL is optional here (Eve's NULL group has a count of 1), but it documents intent: a NULL city is not a city.
Problem 2: Second Highest Price
Return the second highest distinct product price. It must return NULL (not an empty result) if there is no second price.
Solution
SELECT MAX(price) AS second_highest
FROM products
WHERE price < (SELECT MAX(price) FROM products);
| second_highest |
|---|
| 300.00 |
Because MAX over zero rows returns one row containing NULL, this naturally satisfies the "return NULL" requirement. The ORDER BY price DESC LIMIT 1 OFFSET 1 approach returns no rows when there is only one product, and returns a duplicate if the top price is tied — use SELECT DISTINCT price in that variant. For the N-th highest, DENSE_RANK() generalises cleanly (see Advanced SQL).
Problem 3: Predict the Output
Without running it, how many rows does each query return?
-- (a)
SELECT * FROM customers WHERE city = NULL;
-- (b)
SELECT * FROM customers WHERE NOT (city = 'London');
-- (c)
SELECT COUNT(city), COUNT(*) FROM customers;
-- (d)
SELECT c.name FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id;
Solution
- (a) 0 rows.
city = NULLisUNKNOWNfor every row, including Eve's. - (b) 2 rows (Asha, Chen). For Eve,
city = 'London'isUNKNOWN, andNOT UNKNOWNis stillUNKNOWN. - (c) 1 row:
4, 5.COUNT(city)skips Eve'sNULL. - (d) 8 rows. Six matched (customer, order) pairs plus Diana and Eve padded with
NULLs. Asha appears three times — aLEFT JOINcan return more rows than the left table.
Problem 4: Customers Who Bought From Both Categories
Find customers who have ordered at least one Electronics product and at least one Furniture product (any order status).
Solution
Approach 1 — group and count distinct categories:
SELECT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE p.category IN ('Electronics', 'Furniture')
GROUP BY c.customer_id, c.name
HAVING COUNT(DISTINCT p.category) = 2;
Approach 2 — INTERSECT:
SELECT o.customer_id FROM orders o
JOIN order_items oi USING (order_id) JOIN products p USING (product_id)
WHERE p.category = 'Electronics'
INTERSECT
SELECT o.customer_id FROM orders o
JOIN order_items oi USING (order_id) JOIN products p USING (product_id)
WHERE p.category = 'Furniture';
| name |
|---|
| Asha Rao |
| Ben Carter |
Ben qualifies only because his cancelled order 105 contained a laptop; add AND o.status <> 'cancelled' and only Asha remains. Clarifying such edge cases is exactly what interviewers want to hear you ask about. (In approach 2, the guest order 107 would contribute a NULL customer_id to the Furniture branch; it is harmless because nothing in the Electronics branch is NULL.)
Problem 5: Find Duplicate Emails, Case-Insensitively
Suppose the UNIQUE constraint on email was missing and someone inserted 'ASHA@example.com'. Write a query to find email addresses that occur more than once ignoring case, and then a statement that would prevent it in future.
Solution
SELECT lower(email) AS email, COUNT(*) AS copies, array_agg(customer_id) AS ids
FROM customers
GROUP BY lower(email)
HAVING COUNT(*) > 1;
Prevention: a unique index on the expression.
CREATE UNIQUE INDEX customers_email_lower_uniq ON customers (lower(email));
A plain UNIQUE (email) treats ASHA@example.com and asha@example.com as different values. (PostgreSQL also offers the citext extension type; MySQL's default collations are already case-insensitive.)
Problem 6: Largest Order Per Customer With Its Total
For each customer who has orders, show their single largest order (by items total) and its total.
Solution
WITH totals AS (
SELECT o.customer_id, o.order_id, SUM(oi.quantity * oi.unit_price) AS total
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.customer_id IS NOT NULL
GROUP BY o.customer_id, o.order_id
)
SELECT t.customer_id, t.order_id, t.total
FROM totals t
WHERE t.total = (SELECT MAX(t2.total) FROM totals t2 WHERE t2.customer_id = t.customer_id)
ORDER BY t.customer_id;
| customer_id | order_id | total |
|---|---|---|
| 1 | 101 | 1350.00 |
| 2 | 105 | 1200.00 |
| 3 | 104 | 300.00 |
This is the "greatest-N-per-group" problem with N = 1. The correlated-subquery approach returns every tied maximum; ROW_NUMBER() in Advanced SQL lets you pick exactly one, and PostgreSQL's DISTINCT ON (customer_id) ... ORDER BY customer_id, total DESC is a compact alternative.
Problem 7: Spot the Bug
A teammate wrote this to show every product and how many units of it were sold in delivered orders, including products with zero sales. Notebook and some others are missing. Why, and how do you fix it?
SELECT p.name, COALESCE(SUM(oi.quantity), 0) AS units
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.product_id
LEFT JOIN orders o ON o.order_id = oi.order_id
WHERE o.status = 'delivered'
GROUP BY p.product_id, p.name;
Solution
The WHERE o.status = 'delivered' filter is on an outer-joined table, so it discards every row where o.status is NULL (Notebook, which has no items) — and it also discards products whose only sales were non-delivered (Desk was only sold in orders 103 shipped and 106 pending). Move the condition into the join. Because the status lives two joins away, join orders inside a parenthesised join, or pre-filter in a subquery:
SELECT p.name, COALESCE(SUM(d.quantity), 0) AS units
FROM products p
LEFT JOIN (
SELECT oi.product_id, oi.quantity
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
WHERE o.status = 'delivered'
) d ON d.product_id = p.product_id
GROUP BY p.product_id, p.name
ORDER BY p.product_id;
| name | units |
|---|---|
| Laptop | 1 |
| Headphones | 3 |
| Desk | 0 |
| Chair | 6 |
| Notebook | 0 |
Headphones: 1 (order 101) + 2 (order 104). Chair: 2 (order 102) + 4 (order 107).
Problem 8: Customers Active in March but Not in April
Return the names of customers who placed an order in March 2026 but none in April 2026. Give two different solutions.
Solution
With EXCEPT:
SELECT c.name FROM customers c JOIN orders o USING (customer_id)
WHERE o.order_date >= '2026-03-01' AND o.order_date < '2026-04-01'
EXCEPT
SELECT c.name FROM customers c JOIN orders o USING (customer_id)
WHERE o.order_date >= '2026-04-01' AND o.order_date < '2026-05-01';
With EXISTS / NOT EXISTS:
SELECT c.name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
AND o.order_date >= '2026-03-01' AND o.order_date < '2026-04-01')
AND NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
AND o.order_date >= '2026-04-01' AND o.order_date < '2026-05-01');
| name |
|---|
| Asha Rao |
Note the half-open date ranges (>= start AND < next_start): they work for date and timestamp columns alike, never double-count a boundary, and — unlike EXTRACT(MONTH FROM order_date) = 3 — can use an index on order_date. The EXCEPT version compares names, so two different customers with the same name would collide; comparing customer_id is safer.
Problem 9: Atomic Order Placement
Write the SQL for placing order 110 for Chen (customer 3): two Chairs at the current price, shipping fee 5.00, and stock decremented — such that if stock is insufficient, nothing is written. Return the new order's items total.
Solution
BEGIN;
INSERT INTO orders (order_id, customer_id, order_date, status, shipping_fee)
VALUES (110, 3, CURRENT_DATE, 'pending', 5.00);
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT 110, product_id, 2, price FROM products WHERE product_id = 4
RETURNING quantity * unit_price AS items_total; -- 240.00
UPDATE products SET stock = stock - 2 WHERE product_id = 4; -- CHECK (stock >= 0) guards oversell
COMMIT;
Copying price into unit_price at order time is deliberate: the order must record what the customer paid, even if the catalogue price changes later. Under concurrency, two sessions could both read enough stock and both decrement; here the CHECK constraint still prevents negative stock because the UPDATE re-evaluates stock - 2 against the latest committed value under a row lock. More on that in Concurrency Control.