On this page

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:

GroupStands forStatementsPurpose
DDLData Definition LanguageCREATE, ALTER, DROP, TRUNCATEDefine the shape of data: tables, columns, constraints, indexes, views
DMLData Manipulation LanguageINSERT, UPDATE, DELETE, MERGEChange the rows inside tables
DQLData Query LanguageSELECTRead data (often lumped into DML)
DCLData Control LanguageGRANT, REVOKEControl who may do what
TCLTransaction Control LanguageBEGIN, COMMIT, ROLLBACK, SAVEPOINTGroup 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:

FeaturePostgreSQLMySQL 8SQLite 3
String concatenation'a' || 'b'CONCAT('a','b') (|| is OR by default)'a' || 'b'
Auto-increment keyGENERATED ... AS IDENTITYAUTO_INCREMENTINTEGER PRIMARY KEY
RETURNING clauseYesNo (MariaDB has it)Yes (3.35+)
FULL OUTER JOINYesNoYes (3.39+)
INTERSECT / EXCEPTYesYes (8.0.31+)Yes
Types enforced strictlyYesMostly (depends on sql_mode)No — "type affinity" unless STRICT tables
Foreign keys enforcedAlwaysInnoDB onlyOnly 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_idcustomerorder_datestatusitems total
101Asha2026-03-01delivered1350.00
102Asha2026-03-15delivered240.00
103Ben2026-03-20shipped420.00
104Chen2026-04-02delivered300.00
105Ben2026-04-10cancelled1200.00
106Asha2026-05-05pending450.00
107(guest)2026-05-06delivered480.00

MySQL and SQLite notes for the schema

  • MySQL: replace text used in UNIQUE/PRIMARY KEY columns with VARCHAR(255) (MySQL cannot index unbounded TEXT without a prefix length). CHECK constraints are only enforced from 8.0.16. DROP TABLE ... CASCADE is accepted but ignored.
  • SQLite: run PRAGMA foreign_keys = ON; first or the REFERENCES clauses 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.

KindPostgreSQL typesNotes
Integerssmallint (2 B), integer (4 B), bigint (8 B)Use bigint for IDs in tables that may exceed 2.1 billion rows
Exact decimalsnumeric(p, s)Money goes here. Never use float for currency
Floating pointreal, double precisionFast, approximate — 0.1 + 0.2 <> 0.3
Texttext, varchar(n), char(n)In PostgreSQL, text and varchar perform identically; varchar(n) just adds a length check
BooleanbooleanMySQL aliases it to TINYINT(1)
Date/timedate, time, timestamp, timestamptz, intervalPrefer timestamptz for events — it stores an absolute instant
Identifiersuuid16 bytes; v7 UUIDs are time-ordered and index-friendly
Semi-structuredjsonb, 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.

ConstraintExampleWhat it guarantees
NOT NULLname text NOT NULLThe column always has a value
UNIQUEemail text UNIQUENo two rows share the value (multiple NULLs are allowed in most engines)
PRIMARY KEYcustomer_id integer PRIMARY KEYUNIQUE + NOT NULL; the row's identity
FOREIGN KEYREFERENCES customers (customer_id)The value exists in the parent table (or is NULL)
CHECKCHECK (price >= 0)An arbitrary boolean condition on the row
DEFAULTDEFAULT CURRENT_DATEValue 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:

ActionOn DELETE of parentTypical use
NO ACTION / RESTRICT (default)Error if children existMost relationships — make deletion an explicit decision
CASCADEDelete the children tooPure "part-of" relationships: order_items belong to an order
SET NULLSet the child's FK to NULLOptional relationships: an employee whose manager leaves
SET DEFAULTSet the child's FK to its defaultRare

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_idlabel
1sale
2new

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:

StatementRemovesSpeedFires row triggersCan filter rowsTransactional (PostgreSQL)
DELETE FROM t WHERE ...Selected rowsRow by rowYesYesYes
TRUNCATE tAll rows, keeps the tableNear-instantNoNoYes (MySQL: implicit commit)
DROP TABLE tThe table itself, its indexes, its dataNear-instantNoNoYes (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_idnameprice
3Desk330.00
4Chair132.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:

SymptomExplanation from the logical order
WHERE total > 100 fails when total is a SELECT aliasWHERE (step 2) runs before SELECT (step 5) creates the alias
ORDER BY total does work with the aliasORDER BY (step 7) runs after SELECT
WHERE COUNT(*) > 1 is an errorAggregates do not exist until GROUP BY (step 3); filter groups with HAVING
SELECT name, COUNT(*) ... GROUP BY city is an errorAfter 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;
  1. FROM/JOIN pairs each order with its customer: 6 rows (guest order 107 has no match).
  2. WHERE drops order 105 (cancelled): 5 rows.
  3. GROUP BY city makes groups: Mumbai {101, 102, 106}, London {103}, Singapore {104}.
  4. HAVING keeps groups with at least one row: all three.
  5. SELECT computes city and COUNT(*) per group.
  6. ORDER BY sorts by count descending, then city.
cityorders
Mumbai3
London1
Singapore1

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';
namecity
Asha RaoMumbai
Chen WeiSingapore

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":

aba AND ba OR bNOT a
TRUEUNKNOWNUNKNOWNTRUEFALSE
FALSEUNKNOWNFALSEUNKNOWNTRUE
UNKNOWNUNKNOWNUNKNOWNUNKNOWNUNKNOWN

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 NULL gives NULL: 5 + NULL is NULL, and 'Hi ' || NULL is NULL in PostgreSQL.
  • Aggregates skip NULLs: SUM, AVG, MIN, MAX, COUNT(col) ignore them (more in Aggregation).
  • GROUP BY and DISTINCT treat all NULLs as one group — the one place SQL considers NULLs "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;
namecityowner
Asha RaoMumbaiAPAC team
Ben CarterLondonUK team
Chen WeiSingaporeAPAC team
Diana OrtizLondonUK team
Eve AdamsUnknownneeds 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;
namecity
Ben CarterLondon
Diana OrtizLondon
Asha RaoMumbai
Chen WeiSingapore
Eve AdamsNULL

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_idorder_date
1072026-05-06
1062026-05-05
1052026-04-10

OFFSET 3 gives page 2: 104, 103, 102. Two rules:

  1. Always ORDER BY a unique combination when paginating. If two rows tie on order_date, their relative order can change between queries, so a row may appear on two pages or none. Adding order_id as a tie-breaker makes the order total.
  2. OFFSET does not skip work. OFFSET 100000 makes 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_rowswith_customerdistinct_customers
763
  • COUNT(*) counts rows, period.
  • COUNT(customer_id) counts rows where customer_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;
categoryrevenueunits
Electronics1800.005
Furniture1440.009

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_idtotal
1011350.00
1051200.00
107480.00
106450.00
103420.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;
monthorders
2026-03-013
2026-04-012
2026-05-012

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.

JoinKeepsUnmatched rows from AUnmatched rows from B
INNER JOINPairs satisfying ONDroppedDropped
LEFT [OUTER] JOINInner result + unmatched AKept, B columns NULLDropped
RIGHT [OUTER] JOINInner result + unmatched BDroppedKept, A columns NULL
FULL [OUTER] JOINInner result + unmatched A + unmatched BKeptKept
CROSS JOINEvery pair (no ON)n/an/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;
nameorder_idstatus
Asha Rao101delivered
Asha Rao102delivered
Ben Carter103shipped
Chen Wei104delivered
Ben Carter105cancelled
Asha Rao106pending

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;
nameorder_id
Asha Rao101
Asha Rao102
Asha Rao106
Ben Carter103
Ben Carter105
Chen Wei104
Diana OrtizNULL
Eve AdamsNULL

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;
nameorder_id
Asha Rao101
Asha Rao102
Ben Carter103
Chen Wei104
Ben Carter105
Asha Rao106
NULL107
Diana OrtizNULL
Eve AdamsNULL

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;
nameorder_id
Asha Rao101
Asha Rao102
Chen Wei104

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;
nameorder_id
Asha Rao101
Asha Rao102
Ben CarterNULL
Chen Wei104
Diana OrtizNULL
Eve AdamsNULL

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;
namedelivered_orders
Asha Rao2
Ben Carter0
Chen Wei1
Diana Ortiz0
Eve Adams0

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;
nameshipping
Asha Rao35.00
Ben Carter20.00
Chen Wei15.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:

  1. Don't join tables you don't need. The query above never used order_items.
  2. 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;
nameshippingitems
Asha Rao20.002040.00
Ben Carter10.001620.00
Chen Wei15.00300.00
  1. 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;
namecategory
Asha RaoElectronics
Asha RaoFurniture
Asha RaoStationery
Ben CarterElectronics

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;
employeemanager
GraceNULL
HariGrace
InesGrace
JamalHari
KimHari
LeoInes
MayaJamal

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;
namesalarymanagermanager_salary
Maya135000.00Jamal130000.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;
nameorder_idproductquantity
Ben Carter103Chair1
Ben Carter103Desk1
Ben Carter105Laptop1

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.

KindReturnsAppears inExample
ScalarOne row, one columnAnywhere a value is allowedWHERE price > (SELECT AVG(price) FROM products)
Row / listOne column, many rowsIN, ANY, ALLWHERE id IN (SELECT ...)
Table (derived table)Any rows and columnsFROM, JOINFROM (SELECT ...) t
ExistenceAnything (only existence matters)EXISTSWHERE 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_idtotal
1011350.00
1051200.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;
nameorder_idorder_date
Asha Rao1062026-05-05
Ben Carter1052026-04-10
Chen Wei1042026-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
OperationMeaningResult
march UNION aprilIn either, duplicates removed1, 2, 3
march UNION ALL aprilIn either, duplicates kept1, 1, 2, 3, 2
march INTERSECT aprilIn both2
march EXCEPT aprilIn March but not April1
april EXCEPT marchIn April but not March3
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:

  • UNION deduplicates, which costs a sort or hash over the whole result. If duplicates are impossible or acceptable, UNION ALL is strictly faster. Defaulting to UNION ALL and choosing UNION deliberately is a good habit.
  • Set operations treat NULLs as equal to each other (like DISTINCT), unlike =.
  • INTERSECT ALL / EXCEPT ALL keep multiplicities (PostgreSQL supports them; MySQL from 8.0.31; SQLite does not). Oracle spells EXCEPT as MINUS.
  • ORDER BY and LIMIT apply 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_idlifetime_value
12060.00
2430.00
3315.00

Asha: (1350 + 10) + (240 + 5) + (450 + 5) = 2060.

Why views are useful:

BenefitExample
ReuseThe "order total" definition lives in one place; nobody re-derives it with the fan-out bug
AbstractionRename or restructure base tables while keeping the view's interface stable
SecurityGrant 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 TABLES affects only tables that exist now; use ALTER DEFAULT PRIVILEGES for 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.

  1. 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 DISTINCT because of fan-out.
  2. WHERE — row filters: status <> 'cancelled'.
  3. GROUP BY — one output row per city.
  4. HAVING — revenue > 500.
  5. SELECT — city, distinct customers, revenue.
  6. 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;
citycustomersrevenue
Mumbai12040.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

MistakeSymptomFix
WHERE col = NULLAlways zero rowsWHERE col IS NULL
NOT IN (subquery) where the subquery can return NULLZero rowsNOT EXISTS
Right-table filter in WHERE after LEFT JOINUnmatched rows vanishMove the filter into ON
Summing a parent column after joining childrenTotals inflatedPre-aggregate children, or drop the join
COUNT(*) after a LEFT JOINZero-match groups report 1COUNT(right_table.pk)
No ORDER BY with LIMITDifferent rows each runAlways order, with a unique tie-breaker
UNION by defaultUnnecessary sort/hashUNION ALL unless you need dedup
Selecting non-grouped columns (MySQL lax mode)Arbitrary valuesEnable ONLY_FULL_GROUP_BY; aggregate or group
float for moneyCents driftnumeric or integer cents
UPDATE / DELETE without WHEREEvery row changedSELECT the WHERE first; run inside BEGIN
Building SQL by string concatenation with user inputSQL injectionParameterised 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

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;
citycustomers
London2

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 = NULL is UNKNOWN for every row, including Eve's.
  • (b) 2 rows (Asha, Chen). For Eve, city = 'London' is UNKNOWN, and NOT UNKNOWN is still UNKNOWN.
  • (c) 1 row: 4, 5. COUNT(city) skips Eve's NULL.
  • (d) 8 rows. Six matched (customer, order) pairs plus Diana and Eve padded with NULLs. Asha appears three times — a LEFT JOIN can 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_idorder_idtotal
11011350.00
21051200.00
3104300.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;
nameunits
Laptop1
Headphones3
Desk0
Chair6
Notebook0

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.