# Code Reference — SQL

_36 pages_

---
# SQL (/docs/sql)



# SQL [#sql]

Queries, schema, advanced SQL.

Browse the sidebar for every cheat sheet in this section.

## Sections [#sections]

* [Examples](./examples)


---

# Aggregate functions (/docs/sql/aggregate)



# Aggregate functions [#aggregate-functions]

*SQL · Reference cheat sheet*

***

## 📋 Overview [#-overview]

Aggregates compute a single value from a set of rows: `COUNT`, `SUM`, `AVG`, `MIN`, `MAX`, plus dialect extras (`STRING_AGG` / `GROUP_CONCAT`, `BOOL_AND`). Combine with `GROUP BY` and filter groups with `HAVING`.

## 🔧 Core concepts [#-core-concepts]

* **COUNT(\*)** — rows; **COUNT(col)** — non-NULL values; **COUNT(DISTINCT col)**.
* **NULL** — most aggregates ignore NULL inputs; `SUM` of no rows → NULL.
* **GROUP BY** — one result row per group key.
* **FILTER** — Postgres: `COUNT(*) FILTER (WHERE …)`.
* **Window vs aggregate** — windows keep row detail; aggregates collapse rows.

## 💡 Examples [#-examples]

```sql
SELECT
  status,
  COUNT(*) AS n,
  COUNT(DISTINCT customer_id) AS customers,
  SUM(total) AS revenue,
  AVG(total) AS avg_total,
  MIN(created_at) AS first_at,
  MAX(created_at) AS last_at
FROM orders
GROUP BY status;

-- Postgres FILTER
SELECT
  COUNT(*) FILTER (WHERE status = 'paid') AS paid,
  COUNT(*) FILTER (WHERE status = 'refunded') AS refunded
FROM orders;

-- Postgres string agg / MySQL group_concat
SELECT customer_id, STRING_AGG(sku, ', ' ORDER BY sku) AS skus   -- Postgres
FROM order_items
GROUP BY customer_id;
-- MySQL: GROUP_CONCAT(sku ORDER BY sku SEPARATOR ', ')
```

## ⚠️ Pitfalls [#️-pitfalls]

* `AVG` of integers may be integer division in some modes — cast to numeric/decimal.
* `SUM`/`AVG` over empty set return NULL, not 0 — use `COALESCE`.
* Mixing aggregated and non-aggregated columns without `GROUP BY` errors (strict modes).
* `COUNT(DISTINCT …)` can be expensive on large tables.
* Distinct aggregates + multiple distincts are costly — consider approximate methods.

## 🔗 Related [#-related]

* [distinct\_group\_by.md](/docs/sql/distinct-group-by)
* [where\_having.md](/docs/sql/where-having)
* [window\_functions.md](/docs/sql/window-functions)
* [nulls.md](/docs/sql/nulls)
* [select.md](/docs/sql/select)
* [functions\_builtin.md](/docs/sql/functions-builtin)


---

# Constraints (/docs/sql/constraints)



# Constraints [#constraints]

*SQL · Reference cheat sheet*

***

## 📋 Overview [#-overview]

Constraints enforce data integrity: `PRIMARY KEY`, `UNIQUE`, `NOT NULL`, `CHECK`, `FOREIGN KEY`, and defaults. Prefer declarative constraints over application-only checks so every client is covered.

## 🔧 Core concepts [#-core-concepts]

| Constraint    | Guarantees                                         |
| ------------- | -------------------------------------------------- |
| `PRIMARY KEY` | Unique + NOT NULL (one per table)                  |
| `UNIQUE`      | No duplicate non-NULL sets (NULL handling differs) |
| `NOT NULL`    | Column always present                              |
| `CHECK`       | Row-level predicate                                |
| `FOREIGN KEY` | References parent key                              |
| `DEFAULT`     | Value when omitted on insert                       |

* **Named constraints** — easier to drop/alter in migrations.
* **Deferral** — Postgres can `DEFERRABLE` FKs to end of transaction.

## 💡 Examples [#-examples]

```sql
CREATE TABLE products (
  id         BIGSERIAL PRIMARY KEY,
  sku        TEXT NOT NULL,
  price_cents INTEGER NOT NULL CHECK (price_cents >= 0),
  status     TEXT NOT NULL DEFAULT 'draft',
  CONSTRAINT products_sku_unique UNIQUE (sku),
  CONSTRAINT products_status_chk CHECK (status IN ('draft', 'live', 'archived'))
);

ALTER TABLE products
  ADD CONSTRAINT products_price_positive CHECK (price_cents > 0);

ALTER TABLE products DROP CONSTRAINT products_status_chk;

-- Composite unique
CREATE TABLE memberships (
  user_id BIGINT NOT NULL,
  org_id  BIGINT NOT NULL,
  PRIMARY KEY (user_id, org_id)
);
```

## ⚠️ Pitfalls [#️-pitfalls]

* MySQL `CHECK` was ignored before 8.0.16 — verify engine/version.
* Multiple NULLs often allowed in `UNIQUE` (Postgres); MySQL/InnoDB unique NULL behavior differs by version.
* Disabling constraints for bulk load must be re-validated afterward.
* Application validation ≠ database constraint — race conditions need DB enforcement.
* Overly strict `CHECK` can block legitimate historical data migrations.

## 🔗 Related [#-related]

* [foreign\_keys.md](/docs/sql/foreign-keys)
* [create\_alter\_drop.md](/docs/sql/create-alter-drop)
* [nulls.md](/docs/sql/nulls)
* [indexes.md](/docs/sql/indexes)
* [data\_types.md](/docs/sql/data-types)
* [normalization.md](/docs/sql/normalization)


---

# CREATE, ALTER, DROP (/docs/sql/create-alter-drop)



# CREATE, ALTER, DROP [#create-alter-drop]

*SQL · Reference cheat sheet*

***

## 📋 Overview [#-overview]

DDL statements define and change schema objects: tables, columns, indexes, and constraints. Prefer additive, reversible migrations. Dialects differ in types, `IF EXISTS`, and online `ALTER` support (Postgres vs MySQL).

## 🔧 Core concepts [#-core-concepts]

* **CREATE** — tables, indexes, views, schemas.
* **ALTER** — add/drop/rename columns, constraints, defaults.
* **DROP** — remove objects; `CASCADE` / `RESTRICT` control dependents.
* **IF EXISTS / IF NOT EXISTS** — idempotent DDL (widely supported).
* **Transactions** — Postgres DDL is transactional; MySQL DDL often commits implicitly.

## 💡 Examples [#-examples]

```sql
CREATE TABLE users (
  id           BIGSERIAL PRIMARY KEY,          -- Postgres
  -- id BIGINT AUTO_INCREMENT PRIMARY KEY,    -- MySQL
  email        VARCHAR(255) NOT NULL UNIQUE,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

ALTER TABLE users
  ADD COLUMN display_name VARCHAR(120),
  ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT TRUE;

ALTER TABLE users RENAME COLUMN display_name TO name;  -- Postgres
-- MySQL: CHANGE/RENAME COLUMN syntax variants by version

ALTER TABLE users DROP COLUMN is_active;

DROP TABLE IF EXISTS sessions;
DROP TABLE old_events CASCADE;  -- also drops dependent views/FKs (careful)
```

```sql
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);  -- Postgres online
-- MySQL: CREATE INDEX … (locks vary by version/engine)
```

## ⚠️ Pitfalls [#️-pitfalls]

* `DROP … CASCADE` can wipe far more than the target table.
* MySQL `ALTER` may rebuild tables and lock writes — plan maintenance windows.
* Changing column types can rewrite data and fail on incompatible values.
* Forgetting `NOT NULL` / defaults on new columns breaks existing row inserts.
* Don’t mix manual DDL with migration tools without a single source of truth.

## 🔗 Related [#-related]

* [constraints.md](/docs/sql/constraints)
* [data\_types.md](/docs/sql/data-types)
* [indexes.md](/docs/sql/indexes)
* [foreign\_keys.md](/docs/sql/foreign-keys)
* [schemas.md](/docs/sql/schemas)
* [views.md](/docs/sql/views)


---

# CTEs (WITH queries) (/docs/sql/cte)



# CTEs (WITH queries) [#ctes-with-queries]

*SQL · Reference cheat sheet*

***

## 📋 Overview [#-overview]

Common Table Expressions (`WITH`) name subqueries for the main statement — clearer than nested derived tables. Postgres supports recursive CTEs well; MySQL 8+ supports recursive CTEs too. Use for multi-step transforms and graph/hierarchy walks.

## 🔧 Core concepts [#-core-concepts]

* **Non-recursive** — `WITH a AS (…), b AS (…) SELECT …`.
* **Recursive** — `WITH RECURSIVE` anchor + recursive member `UNION ALL`.
* **Scope** — CTEs visible to the final statement (and later CTEs).
* **Materialization** — optimizers may inline or materialize (Postgres hints/`MATERIALIZED`).
* **DML** — `WITH … INSERT/UPDATE/DELETE` in many engines.

## 💡 Examples [#-examples]

```sql
WITH paid AS (
  SELECT customer_id, SUM(total) AS revenue
  FROM orders
  WHERE status = 'paid'
  GROUP BY customer_id
),
big AS (
  SELECT * FROM paid WHERE revenue > 1000
)
SELECT u.email, b.revenue
FROM big b
JOIN users u ON u.id = b.customer_id
ORDER BY b.revenue DESC;

-- Recursive org tree (Postgres / MySQL 8+)
WITH RECURSIVE tree AS (
  SELECT id, manager_id, name, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.manager_id, e.name, t.depth + 1
  FROM employees e
  JOIN tree t ON e.manager_id = t.id
)
SELECT * FROM tree ORDER BY depth, name;
```

```sql
-- Postgres: force materialize / inline (PG 12+)
WITH cte AS MATERIALIZED (SELECT …) …
```

## ⚠️ Pitfalls [#️-pitfalls]

* Recursive CTEs without a termination condition can loop — add depth guards.
* Multiple references to a CTE may be recomputed or materialized — check `EXPLAIN`.
* CTE ≠ temp table with indexes — heavy reuse may need real temp tables.
* Column lists in recursive unions must match types/count.
* Older MySQL (\<8) lacks CTEs — use derived tables.

## 🔗 Related [#-related]

* [subquery.md](/docs/sql/subquery)
* [select.md](/docs/sql/select)
* [window\_functions.md](/docs/sql/window-functions)
* [views.md](/docs/sql/views)
* [explain\_analyze.md](/docs/sql/explain-analyze)
* [joins.md](/docs/sql/joins)


---

# Data types (/docs/sql/data-types)



# Data types [#data-types]

*SQL · Reference cheat sheet*

***

## 📋 Overview [#-overview]

Choose types that match domain constraints and indexing needs. Prefer precise numerics for money, `TIMESTAMPTZ` for instants (Postgres), and explicit character types. Dialects differ: Postgres `TEXT`/`JSONB` vs MySQL `VARCHAR`/`JSON`.

## 🔧 Core concepts [#-core-concepts]

| Category | Postgres                                | MySQL (typical)           |
| -------- | --------------------------------------- | ------------------------- |
| Integers | `SMALLINT`/`INT`/`BIGINT`               | same names                |
| Serial   | `GENERATED … AS IDENTITY` / `BIGSERIAL` | `AUTO_INCREMENT`          |
| Decimal  | `NUMERIC(p,s)`                          | `DECIMAL(p,s)`            |
| Float    | `REAL`/`DOUBLE PRECISION`               | `FLOAT`/`DOUBLE`          |
| String   | `TEXT`, `VARCHAR(n)`                    | `VARCHAR(n)`, `TEXT`      |
| Time     | `TIMESTAMPTZ`, `DATE`                   | `DATETIME`, `TIMESTAMP`   |
| Bool     | `BOOLEAN`                               | `TINYINT(1)` / `BOOLEAN`  |
| JSON     | `JSONB`                                 | `JSON`                    |
| UUID     | `UUID`                                  | `BINARY(16)` / `CHAR(36)` |

## 💡 Examples [#-examples]

```sql
-- Postgres
CREATE TABLE payments (
  id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  amount       NUMERIC(12, 2) NOT NULL,
  currency     CHAR(3) NOT NULL,
  paid_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  meta         JSONB NOT NULL DEFAULT '{}'::jsonb,
  external_id  UUID UNIQUE
);

-- MySQL
CREATE TABLE payments (
  id           BIGINT AUTO_INCREMENT PRIMARY KEY,
  amount       DECIMAL(12, 2) NOT NULL,
  currency     CHAR(3) NOT NULL,
  paid_at      DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  meta         JSON NOT NULL,
  external_id  CHAR(36) UNIQUE
);
```

## ⚠️ Pitfalls [#️-pitfalls]

* Floating point for money causes rounding bugs — use `NUMERIC`/`DECIMAL`.
* MySQL `TIMESTAMP` converts to session time zone; `DATETIME` does not — be explicit.
* Oversized `VARCHAR` still has limits/row size issues in MySQL indexes.
* Storing enums as free `TEXT` without constraints invites junk values.
* Changing types on large tables can rewrite storage and lock (plan migrations).

## 🔗 Related [#-related]

* [create\_alter\_drop.md](/docs/sql/create-alter-drop)
* [constraints.md](/docs/sql/constraints)
* [functions\_builtin.md](/docs/sql/functions-builtin)
* [schemas.md](/docs/sql/schemas)
* [nulls.md](/docs/sql/nulls)
* [indexes.md](/docs/sql/indexes)


---

# DISTINCT and GROUP BY (/docs/sql/distinct-group-by)



# DISTINCT and GROUP BY [#distinct-and-group-by]

*SQL · Reference cheat sheet*

***

## 📋 Overview [#-overview]

`DISTINCT` removes duplicate result rows. `GROUP BY` collapses rows by key and pairs with aggregates. Don’t use `DISTINCT` to paper over bad joins — fix the join or group explicitly.

## 🔧 Core concepts [#-core-concepts]

* **SELECT DISTINCT** — unique across the selected expression list.
* **DISTINCT ON** — Postgres: first row per expressions (needs matching `ORDER BY`).
* **GROUP BY** — group keys; other selected columns must be aggregated (strict SQL).
* **Functional dependency** — Postgres may allow PK columns when grouping by PK.
* **GROUPING SETS / ROLLUP / CUBE** — multiple groupings in one pass (dialect support varies).

## 💡 Examples [#-examples]

```sql
SELECT DISTINCT country FROM users;

SELECT customer_id, COUNT(*) AS orders
FROM orders
GROUP BY customer_id;

-- Postgres DISTINCT ON (latest order per customer)
SELECT DISTINCT ON (customer_id)
  customer_id, id, created_at, total
FROM orders
ORDER BY customer_id, created_at DESC;

-- MySQL equivalent pattern: window or join to max(created_at)

SELECT region, status, COUNT(*)
FROM orders
GROUP BY ROLLUP (region, status);  -- Postgres; MySQL has WITH ROLLUP
```

## ⚠️ Pitfalls [#️-pitfalls]

* `SELECT DISTINCT a, b` is not “distinct a” — uniqueness is on the whole row.
* `ONLY_FULL_GROUP_BY` (MySQL) rejects ambiguous non-aggregated columns.
* `DISTINCT` + `ORDER BY` expressions not in select list can error.
* Using `DISTINCT` to fix row multiplication from joins hides cardinality bugs.
* `COUNT(DISTINCT x)` vs `DISTINCT` then `COUNT` — different plans/costs.

## 🔗 Related [#-related]

* [aggregate.md](/docs/sql/aggregate)
* [where\_having.md](/docs/sql/where-having)
* [select.md](/docs/sql/select)
* [order\_limit.md](/docs/sql/order-limit)
* [window\_functions.md](/docs/sql/window-functions)
* [joins.md](/docs/sql/joins)


---

# Examples (/docs/sql/examples)



# Examples [#examples]

SQL notes in **Examples**.


---

# Report Join (/docs/sql/examples/report-join)



# Report Join [#report-join]

*SQL · Example / how-to*

***

## 📋 Overview [#-overview]

Build a sales report by joining orders, customers, and products, then aggregating totals per customer.

## 🔧 Core concepts [#-core-concepts]

| Piece        | Role                          |
| ------------ | ----------------------------- |
| `INNER JOIN` | Match related rows            |
| `LEFT JOIN`  | Keep parents without children |
| `GROUP BY`   | Aggregate per key             |
| `ORDER BY`   | Rank the report               |

## 💡 Examples [#-examples]

```sql
-- Schema sketch
-- customers(id, name)
-- products(id, name, unit_price)
-- orders(id, customer_id, created_at)
-- order_items(id, order_id, product_id, qty)

SELECT
  c.id AS customer_id,
  c.name AS customer_name,
  COUNT(DISTINCT o.id) AS order_count,
  COALESCE(SUM(oi.qty * p.unit_price), 0) AS revenue
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id
LEFT JOIN order_items AS oi
  ON oi.order_id = o.id
LEFT JOIN products AS p
  ON p.id = oi.product_id
WHERE o.created_at >= DATE '2026-01-01'
   OR o.id IS NULL
GROUP BY c.id, c.name
ORDER BY revenue DESC, customer_name ASC;
```

**Only customers with orders:**

```sql
SELECT c.name, SUM(oi.qty * p.unit_price) AS revenue
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
INNER JOIN order_items oi ON oi.order_id = o.id
INNER JOIN products p ON p.id = oi.product_id
GROUP BY c.name
HAVING SUM(oi.qty * p.unit_price) > 100
ORDER BY revenue DESC;
```

## ⚠️ Pitfalls [#️-pitfalls]

* Joining to line items before aggregating can duplicate order-level rows.
* `WHERE` on the right table of a `LEFT JOIN` can accidentally turn it into an inner join.
* Always qualify columns (`c.name`) when names collide.

## 🔗 Related [#-related]

* [Upsert inventory](/docs/sql/examples/upsert-inventory)
* [Window rank](/docs/sql/examples/window-rank)


---

# Upsert Inventory (/docs/sql/examples/upsert-inventory)



# Upsert Inventory [#upsert-inventory]

*SQL · Example / how-to*

***

## 📋 Overview [#-overview]

Insert or update inventory quantities atomically with `INSERT ... ON CONFLICT` (PostgreSQL) or equivalent upsert patterns.

## 🔧 Core concepts [#-core-concepts]

| Piece         | Role                          |
| ------------- | ----------------------------- |
| Unique key    | Conflict target (SKU)         |
| `ON CONFLICT` | Upsert branch                 |
| `EXCLUDED`    | Proposed row values           |
| Atomicity     | Avoid racey read-modify-write |

## 💡 Examples [#-examples]

**PostgreSQL:**

```sql
CREATE TABLE inventory (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  qty INTEGER NOT NULL CHECK (qty >= 0),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

INSERT INTO inventory (sku, name, qty)
VALUES ('ABC-1', 'Widget', 10)
ON CONFLICT (sku) DO UPDATE
SET
  name = EXCLUDED.name,
  qty = inventory.qty + EXCLUDED.qty,
  updated_at = NOW()
RETURNING sku, name, qty, updated_at;
```

**Set absolute quantity instead of increment:**

```sql
INSERT INTO inventory (sku, name, qty)
VALUES ('ABC-1', 'Widget', 25)
ON CONFLICT (sku) DO UPDATE
SET name = EXCLUDED.name,
    qty = EXCLUDED.qty,
    updated_at = NOW();
```

**SQLite (similar):**

```sql
INSERT INTO inventory (sku, name, qty)
VALUES ('ABC-1', 'Widget', 10)
ON CONFLICT(sku) DO UPDATE SET
  qty = inventory.qty + excluded.qty,
  name = excluded.name;
```

## ⚠️ Pitfalls [#️-pitfalls]

* Without a unique constraint, `ON CONFLICT` cannot target the row.
* Increment vs replace semantics differ — document which you mean.
* Check constraints still apply on the final row after update.

## 🔗 Related [#-related]

* [Report join](/docs/sql/examples/report-join)
* [Window rank](/docs/sql/examples/window-rank)


---

# Window Rank (/docs/sql/examples/window-rank)



# Window Rank [#window-rank]

*SQL · Example / how-to*

***

## 📋 Overview [#-overview]

Rank rows within partitions using window functions (`RANK`, `DENSE_RANK`, `ROW_NUMBER`) for leaderboards and top-N-per-group queries.

## 🔧 Core concepts [#-core-concepts]

| Piece                   | Role                     |
| ----------------------- | ------------------------ |
| `OVER (PARTITION BY …)` | Group without collapsing |
| `ORDER BY` in window    | Rank order               |
| `ROW_NUMBER`            | Unique sequence          |
| `RANK` / `DENSE_RANK`   | Tie handling             |

## 💡 Examples [#-examples]

```sql
-- scores(player_id, game, points, played_at)

SELECT
  player_id,
  game,
  points,
  ROW_NUMBER() OVER (
    PARTITION BY game
    ORDER BY points DESC, played_at ASC
  ) AS row_num,
  RANK() OVER (
    PARTITION BY game
    ORDER BY points DESC
  ) AS rank,
  DENSE_RANK() OVER (
    PARTITION BY game
    ORDER BY points DESC
  ) AS dense_rank
FROM scores;
```

**Top 3 per game:**

```sql
WITH ranked AS (
  SELECT
    player_id,
    game,
    points,
    ROW_NUMBER() OVER (
      PARTITION BY game
      ORDER BY points DESC, played_at ASC
    ) AS rn
  FROM scores
)
SELECT player_id, game, points
FROM ranked
WHERE rn <= 3
ORDER BY game, rn;
```

## ⚠️ Pitfalls [#️-pitfalls]

* `RANK` skips numbers after ties; `DENSE_RANK` does not; `ROW_NUMBER` never ties.
* Filtering on window results needs a subquery/CTE — `WHERE rank &lt;= 3` is invalid on the same select level in most engines.
* Missing `ORDER BY` in `OVER` makes ranking nondeterministic.

## 🔗 Related [#-related]

* [Report join](/docs/sql/examples/report-join)
* [Upsert inventory](/docs/sql/examples/upsert-inventory)


---

# EXPLAIN and ANALYZE (/docs/sql/explain-analyze)



# EXPLAIN and ANALYZE [#explain-and-analyze]

*SQL · Reference cheat sheet*

***

## 📋 Overview [#-overview]

`EXPLAIN` shows the optimizer’s plan; `EXPLAIN ANALYZE` (Postgres) / `EXPLAIN ANALYZE` (MySQL 8.0.18+) executes the query and reports actual timings/rows. Use it to find sequential scans, bad join order, and misestimates.

## 🔧 Core concepts [#-core-concepts]

* **Nodes** — seq scan, index scan, nested loop, hash join, sort, aggregate.
* **Estimates vs actual** — large gaps ⇒ stale stats or correlated columns.
* **Buffers / IO** — Postgres `BUFFERS`; track hot reads.
* **Format** — `JSON`/`TEXT`/`YAML` (Postgres); tree vs traditional (MySQL).
* **Stats** — `ANALYZE` table (Postgres) / `ANALYZE TABLE` (MySQL) refresh stats.

## 💡 Examples [#-examples]

```sql
-- Postgres
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.*
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.created_at >= NOW() - INTERVAL '7 days';

ANALYZE orders;  -- refresh statistics
```

```sql
-- MySQL
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;
ANALYZE TABLE orders;
```

## ⚠️ Pitfalls [#️-pitfalls]

* `EXPLAIN ANALYZE` runs the query — careful with writes/`UPDATE`/`DELETE` (use transactions + rollback where possible).
* Reading only estimated rows without ANALYZE misleads on skewed data.
* Missing indexes show as seq scans — confirm selectivity before indexing everything.
* Parameterized app queries may get different plans than literal `EXPLAIN` — use actual params.
* Caching effects make second runs look faster — warm vs cold cache matters.

## 🔗 Related [#-related]

* [indexes.md](/docs/sql/indexes)
* [select.md](/docs/sql/select)
* [joins.md](/docs/sql/joins)
* [order\_limit.md](/docs/sql/order-limit)
* [cte.md](/docs/sql/cte)
* [subquery.md](/docs/sql/subquery)


---

# Foreign keys (/docs/sql/foreign-keys)



# Foreign keys [#foreign-keys]

*SQL · Reference cheat sheet*

***

## 📋 Overview [#-overview]

Foreign keys enforce that values in a child table reference an existing parent key. They document relationships and prevent orphans. Define `ON DELETE` / `ON UPDATE` actions explicitly. Index FK columns for join/delete performance.

## 🔧 Core concepts [#-core-concepts]

* **References** — `FOREIGN KEY (child_col) REFERENCES parent (col)`.
* **Actions** — `NO ACTION`/`RESTRICT`, `CASCADE`, `SET NULL`, `SET DEFAULT`.
* **Composite FKs** — match multi-column parent unique/PK keys.
* **Deferral** — Postgres `DEFERRABLE INITIALLY DEFERRED` checks at commit.
* **Match** — usually `MATCH SIMPLE` (NULLs allowed in FK cols unless NOT NULL).

## 💡 Examples [#-examples]

```sql
CREATE TABLE customers (
  id BIGSERIAL PRIMARY KEY
);

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  customer_id BIGINT NOT NULL,
  CONSTRAINT orders_customer_fk
    FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
);

CREATE INDEX orders_customer_id_idx ON orders (customer_id);

-- Cascade delete children
ALTER TABLE order_items
  ADD CONSTRAINT order_items_order_fk
  FOREIGN KEY (order_id) REFERENCES orders(id)
  ON DELETE CASCADE;

-- Soft-friendly: SET NULL
ALTER TABLE posts
  ADD CONSTRAINT posts_author_fk
  FOREIGN KEY (author_id) REFERENCES users(id)
  ON DELETE SET NULL;
```

## ⚠️ Pitfalls [#️-pitfalls]

* Missing indexes on FK columns make `ON DELETE` / joins slow (especially Postgres).
* `ON DELETE CASCADE` can wipe large subgraphs — prefer explicit deletes in app code for critical data.
* MySQL: FKs require InnoDB; type/signedness must match parent exactly.
* Circular FKs need deferred checks or staged inserts.
* Orphan prevention doesn’t replace soft-delete policies — decide deleted parent behavior.

## 🔗 Related [#-related]

* [constraints.md](/docs/sql/constraints)
* [normalization.md](/docs/sql/normalization)
* [indexes.md](/docs/sql/indexes)
* [create\_alter\_drop.md](/docs/sql/create-alter-drop)
* [insert\_update\_delete.md](/docs/sql/insert-update-delete)
* [joins.md](/docs/sql/joins)


---

# Built-in functions (/docs/sql/functions-builtin)



# Built-in functions [#built-in-functions]

*SQL · Reference cheat sheet*

***

## 📋 Overview [#-overview]

SQL provides scalar functions for strings, numbers, dates, JSON, and conditionals. Names and behavior differ across Postgres and MySQL — verify docs for edge cases (`ILIKE`, `DATE_TRUNC`, `IFNULL` vs `COALESCE`).

## 🔧 Core concepts [#-core-concepts]

| Area        | Common functions                                                                  |
| ----------- | --------------------------------------------------------------------------------- |
| String      | `CONCAT`, `LENGTH`/`CHAR_LENGTH`, `SUBSTRING`, `TRIM`, `REPLACE`, `LOWER`/`UPPER` |
| Numeric     | `ABS`, `ROUND`, `CEIL`/`FLOOR`, `MOD`, `POWER`                                    |
| Date/time   | `NOW`/`CURRENT_TIMESTAMP`, `DATE_TRUNC` (PG), `DATE_FORMAT` (MySQL), `AGE`        |
| Conditional | `COALESCE`, `NULLIF`, `CASE`, `GREATEST`/`LEAST`                                  |
| JSON        | `->`/`->>` (PG), `JSON_EXTRACT` (MySQL)                                           |
| Cast        | `CAST(x AS type)`, `x::type` (Postgres)                                           |

## 💡 Examples [#-examples]

```sql
SELECT
  LOWER(email) AS email_norm,
  CONCAT(first_name, ' ', last_name) AS full_name,
  ROUND(price * 1.1, 2) AS with_tax,
  COALESCE(nickname, first_name) AS label
FROM users;

-- Postgres dates
SELECT DATE_TRUNC('day', created_at) AS day, COUNT(*)
FROM events
GROUP BY 1;

-- MySQL dates
SELECT DATE_FORMAT(created_at, '%Y-%m-%d') AS day, COUNT(*)
FROM events
GROUP BY day;

SELECT CASE
  WHEN total >= 100 THEN 'gold'
  WHEN total >= 50 THEN 'silver'
  ELSE 'bronze'
END AS tier
FROM orders;

-- Postgres JSON
SELECT payload->>'userId' AS user_id FROM events;
-- MySQL: JSON_UNQUOTE(JSON_EXTRACT(payload, '$.userId'))
```

## ⚠️ Pitfalls [#️-pitfalls]

* `LENGTH` in Postgres is bytes for `bytea` / sometimes confusing with multibyte — prefer `CHAR_LENGTH` for characters.
* Implicit casts hide type bugs — prefer explicit `CAST`.
* Time zones: `TIMESTAMP` vs `TIMESTAMPTZ` (Postgres) — store UTC consciously.
* MySQL `ONLY_FULL_GROUP_BY` interacts with select-list functions + aliases.
* Non-immutable functions in indexes/generated columns may be disallowed.

## 🔗 Related [#-related]

* [data\_types.md](/docs/sql/data-types)
* [aggregate.md](/docs/sql/aggregate)
* [nulls.md](/docs/sql/nulls)
* [select.md](/docs/sql/select)
* [where\_having.md](/docs/sql/where-having)
* [window\_functions.md](/docs/sql/window-functions)


---

# Getting Started with SQL (/docs/sql/getting-started)



# Getting Started with SQL [#getting-started-with-sql]

*SQL · Reference cheat sheet*

***

## 📋 Overview [#-overview]

SQL (Structured Query Language) is how you talk to relational databases: create tables, insert rows, and query data. Dialects (PostgreSQL, MySQL, SQLite, SQL Server) share a core language with small differences.

## 🔧 Core concepts [#-core-concepts]

| Idea           | Meaning                              |
| -------------- | ------------------------------------ |
| Database       | Named collection of schemas/tables   |
| Table          | Rows + named columns                 |
| Row / record   | One entry                            |
| Column / field | One attribute with a data type       |
| Query          | Statement that reads or changes data |
| Primary key    | Uniquely identifies a row            |

Beginners often practice on **SQLite** (a file DB) or a free Postgres instance.

## 💡 Examples [#-examples]

**SQLite quick start:**

```shellscript
sqlite3 practice.db
```

```sql
SELECT 'Hello, SQL!' AS message;
```

**Create a tiny table and query it:**

```sql
CREATE TABLE friends (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

INSERT INTO friends (name) VALUES ('Ada'), ('Grace');
SELECT * FROM friends;
```

**Filter rows:**

```sql
SELECT name
FROM friends
WHERE name LIKE 'A%';
```

## ⚠️ Pitfalls [#️-pitfalls]

* SQL keywords are case-insensitive by convention; `SELECT` vs `select` both work.
* String quotes are single quotes in standard SQL: `'Ada'`, not `"Ada"` (double quotes often mean identifiers).
* Each dialect has quirks (`AUTOINCREMENT`, `SERIAL`, `LIMIT` vs `TOP`).
* Never paste untrusted input into SQL strings — use parameterized queries in apps.

## 🔗 Related [#-related]

* [hello\_world.md](/docs/sql/hello-world)
* [tables\_basics.md](/docs/sql/tables-basics)
* [primary\_keys\_basics.md](/docs/sql/primary-keys-basics)
* [null\_basics.md](/docs/sql/null-basics)


---

# Glossary (/docs/sql/glossary)



# Glossary [#glossary]

*SQL · Reference cheat sheet*

***

## 📋 Overview [#-overview]

Alphabetical glossary of core SQL terms for querying, schema design, transactions, and relational database concepts.

## 🔧 Core concepts [#-core-concepts]

| Term            | Definition                                                                         |
| --------------- | ---------------------------------------------------------------------------------- |
| Aggregate       | A function that summarizes many rows into one value, such as `COUNT` or `SUM`.     |
| Alias           | A temporary name for a table or column using `AS`.                                 |
| Constraint      | A rule enforcing data integrity, such as `NOT NULL`, `UNIQUE`, or `CHECK`.         |
| CTE             | A Common Table Expression defined with `WITH` as a named subquery.                 |
| DDL             | Data Definition Language statements like `CREATE`, `ALTER`, and `DROP`.            |
| DML             | Data Manipulation Language statements like `SELECT`, `INSERT`, `UPDATE`, `DELETE`. |
| Foreign key     | A column referencing a primary key in another table.                               |
| Group by        | A clause that clusters rows so aggregates compute per group.                       |
| Having          | A filter applied after aggregation, unlike `WHERE` which filters rows first.       |
| Index           | A data structure that speeds lookups at the cost of write overhead.                |
| Join            | Combining rows from two tables based on a related column condition.                |
| Normalization   | Organizing tables to reduce redundancy and improve integrity.                      |
| Null            | A marker for missing or unknown data, not equal to any value including itself.     |
| Order by        | A clause that sorts the result set by one or more expressions.                     |
| Primary key     | A unique, non-null identifier for each row in a table.                             |
| Schema          | The namespace/structure of tables, types, and constraints in a database.           |
| Select          | The statement that retrieves rows and columns from tables or views.                |
| Subquery        | A query nested inside another statement’s `FROM`, `WHERE`, or `SELECT`.            |
| Table           | A named relation storing rows with a fixed set of columns.                         |
| Transaction     | A unit of work that commits or rolls back as a whole (ACID).                       |
| Trigger         | Procedural code that runs automatically on insert/update/delete events.            |
| Union           | Combining result sets of compatible queries into one set of rows.                  |
| Upsert          | Insert-or-update behavior, often via `ON CONFLICT` or `MERGE`.                     |
| View            | A stored query presented as a virtual table.                                       |
| Where           | A clause that filters rows before grouping.                                        |
| Window function | A function that computes across related rows without collapsing them.              |

## 💡 Examples [#-examples]

**Select, where, and join:**

```sql
SELECT u.name, o.total
FROM users AS u
JOIN orders AS o ON o.user_id = u.id
WHERE o.total > 100
ORDER BY o.total DESC;
```

**CTE and window function:**

```sql
WITH ranked AS (
  SELECT name, salary,
         RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT * FROM ranked WHERE rnk <= 3;
```

**Transaction:**

```sql
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT;
```

## ⚠️ Pitfalls [#️-pitfalls]

* Confusing **WHERE** (pre-aggregate filter) with **HAVING** (post-aggregate filter).
* Mixing **INNER JOIN** and **LEFT JOIN** expectations about unmatched rows.
* Treating **NULL** like a value — use `IS NULL`, not `= NULL`.
* Equating a **view** with a physical table — views are stored queries (unless materialized).
* Assuming **UNION** and **UNION ALL** are identical — `UNION` deduplicates.

## 🔗 Related [#-related]

* [select](/docs/sql/select)
* [joins](/docs/sql/joins)
* [cte](/docs/sql/cte)
* [transactions](/docs/sql/transactions)
* [window\_functions](/docs/sql/window-functions)
* [where\_having](/docs/sql/where-having)


---

# Hello World (/docs/sql/hello-world)



# Hello World [#hello-world]

*SQL · Reference cheat sheet*

***

## 📋 Overview [#-overview]

SQL Hello World is usually a `SELECT` that returns a constant, or creating a one-column table and reading it back. It proves your client can connect and run statements.

## 🔧 Core concepts [#-core-concepts]

| Piece      | Role                                  |
| ---------- | ------------------------------------- |
| `SELECT`   | Read data / expressions               |
| `AS`       | Alias a column name in the result     |
| Statement  | One SQL command (often ends with `;`) |
| Result set | Table-like output of a query          |

Many tools (sqlite3, psql, GUI clients) show results as a grid.

## 💡 Examples [#-examples]

**Constant select:**

```sql
SELECT 'Hello, World!' AS greeting;
```

**Math and aliases:**

```sql
SELECT 2 + 2 AS four;
SELECT 'Ada' AS name, 1815 AS birth_year;
```

**Round-trip with a table (SQLite-friendly):**

```sql
CREATE TABLE hello (
  id INTEGER PRIMARY KEY,
  message TEXT NOT NULL
);

INSERT INTO hello (message) VALUES ('Hello, World!');
SELECT message FROM hello;
```

**Current time (Postgres example):**

```sql
SELECT NOW() AS right_now;
```

## ⚠️ Pitfalls [#️-pitfalls]

* Forgetting `;` in interactive clients that require it.
* Connecting to the wrong database/schema and thinking the query “failed.”
* `SELECT *` is fine for learning; be specific in real apps.
* Dialect functions differ (`NOW()`, `datetime('now')`, `GETDATE()`).

## 🔗 Related [#-related]

* [getting\_started.md](/docs/sql/getting-started)
* [tables\_basics.md](/docs/sql/tables-basics)
* [primary\_keys\_basics.md](/docs/sql/primary-keys-basics)
* [null\_basics.md](/docs/sql/null-basics)


---

# Indexes (/docs/sql/indexes)



# Indexes [#indexes]

**SQL · Reference cheat sheet**

***

## 📖 Overview [#-overview]

Indexes speed lookups, joins, and sorts by maintaining ordered structures (commonly B-trees) over column values. They trade write overhead and storage for faster reads. Design indexes around real query predicates and join keys.

## 🧩 Core concepts [#-core-concepts]

* **B-tree** — default for equality and range (`=`, `&lt;`, `BETWEEN`, `ORDER BY`).
* **Unique / PK / FK** — uniqueness constraints create indexes; FKs often need supporting indexes on the child column.
* **Composite indexes** — leftmost prefix rule: `(a, b)` helps `a` and `a,b`, not `b` alone.
* **Covering / INCLUDE** — index-only scans when all needed columns are in the index.
* **Partial / filtered** — index a subset (`WHERE deleted_at IS NULL`).
* **Hash / GIN / GiST / columnstore** — engine-specific for equality-only, JSON, full-text, analytics.

## 💡 Examples [#-examples]

```sql
-- Single-column
CREATE INDEX idx_users_email ON users (email);

-- Unique
CREATE UNIQUE INDEX ux_users_email ON users (email);

-- Composite (status filtered lists by created_at)
CREATE INDEX idx_orders_status_created
  ON orders (status, created_at DESC);

-- Partial (PostgreSQL)
CREATE INDEX idx_users_active_email
  ON users (email)
  WHERE active = TRUE;

-- Covering-ish with INCLUDE (PostgreSQL)
CREATE INDEX idx_orders_user_covering
  ON orders (user_id)
  INCLUDE (status, total);

-- Inspect (examples)
-- PostgreSQL: EXPLAIN ANALYZE SELECT …;
-- MySQL:      EXPLAIN SELECT …;
```

## ⚠️ Pitfalls [#️-pitfalls]

* Too many indexes slow `INSERT`/`UPDATE`/`DELETE` and bloat storage.
* Functions on columns (`WHERE LOWER(email) = …`) need matching expression indexes.
* Low-selectivity columns (`boolean`) rarely help alone — combine with selective columns.
* Unused indexes still cost writes — review with engine stats (`pg_stat_user_indexes`, etc.).

## 🔗 Related [#-related]

* [Select](/docs/sql/select)
* [Joins](/docs/sql/joins)
* [Transactions](/docs/sql/transactions)
* [Insert / Update / Delete](/docs/sql/insert-update-delete)


---

# Insert, Update, Delete (/docs/sql/insert-update-delete)



# Insert, Update, Delete [#insert-update-delete]

**SQL · Reference cheat sheet**

***

## 📖 Overview [#-overview]

Data-modification statements change table contents: `INSERT` adds rows, `UPDATE` changes existing values, `DELETE` removes rows. Always scope `UPDATE`/`DELETE` with a precise `WHERE` (or use transactions and `RETURNING` where supported).

## 🧩 Core concepts [#-core-concepts]

* **INSERT** — single row, multi-row, or `INSERT … SELECT`.
* **UPSERT** — `ON CONFLICT` (Postgres) / `ON DUPLICATE KEY` (MySQL) / `MERGE`.
* **UPDATE** — set columns; optional `FROM`/`JOIN` in some dialects.
* **DELETE** — remove matching rows; `TRUNCATE` is bulk and minimally logged (dialect-specific).
* **RETURNING** — get affected rows back (Postgres, SQLite, …).
* **Constraints** — PK/FK/UNIQUE/CHECK can reject writes.

## 💡 Examples [#-examples]

```sql
-- Insert
INSERT INTO users (email, name)
VALUES ('ada@example.com', 'Ada');

INSERT INTO users (email, name)
VALUES
  ('al@example.com', 'Al'),
  ('bo@example.com', 'Bo');

INSERT INTO users (email, name)
SELECT email, full_name FROM staging_users
WHERE email IS NOT NULL;

-- Upsert (PostgreSQL)
INSERT INTO settings (user_id, theme)
VALUES (42, 'dark')
ON CONFLICT (user_id)
DO UPDATE SET theme = EXCLUDED.theme, updated_at = NOW();

-- Update
UPDATE orders
SET status = 'shipped', shipped_at = NOW()
WHERE id = 1001 AND status = 'paid'
RETURNING id, status;

-- Delete
DELETE FROM sessions
WHERE expires_at < NOW();

-- Careful bulk clear (cannot easily undo)
-- TRUNCATE TABLE sessions;
```

## ⚠️ Pitfalls [#️-pitfalls]

* Missing `WHERE` on `UPDATE`/`DELETE` can wipe the whole table — run inside a transaction first.
* Multi-row inserts may partially fail depending on constraints and dialect abort behavior.
* Triggers and cascading FKs can delete/update far more than the statement text suggests.
* `TRUNCATE` often cannot run inside the same transactional assumptions as `DELETE` (and may need privileges).

## 🔗 Related [#-related]

* [Select](/docs/sql/select)
* [Transactions](/docs/sql/transactions)
* [Joins](/docs/sql/joins)
* [Indexes](/docs/sql/indexes)


---

# Joins (/docs/sql/joins)



# Joins [#joins]

**SQL · Reference cheat sheet**

***

## 📖 Overview [#-overview]

Joins combine rows from multiple tables using match conditions. Pick the join type based on whether unmatched rows from either side should appear. Prefer explicit `JOIN … ON` over old comma-style joins.

## 🧩 Core concepts [#-core-concepts]

* **INNER JOIN** — only matching pairs.
* **LEFT / RIGHT JOIN** — keep all rows from one side; pad the other with `NULL`.
* **FULL OUTER JOIN** — keep unmatched rows from both sides (not in MySQL historically).
* **CROSS JOIN** — Cartesian product.
* **Self join** — same table aliased twice (hierarchies, comparisons).
* **Anti/semi patterns** — `NOT EXISTS` / `LEFT JOIN … WHERE right.key IS NULL` for “missing” rows.

## 💡 Examples [#-examples]

```sql
-- Inner
SELECT o.id, u.email, o.total
FROM orders o
INNER JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid';

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

-- Anti-join: users with no orders
SELECT u.*
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

-- Equivalent anti-join
SELECT u.*
FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- Multiple joins
SELECT o.id, u.email, p.sku
FROM orders o
JOIN users u ON u.id = o.user_id
JOIN order_items i ON i.order_id = o.id
JOIN products p ON p.id = i.product_id;
```

## ⚠️ Pitfalls [#️-pitfalls]

* Filtering a `LEFT JOIN`ed table in `WHERE` can accidentally turn it into an inner join — put right-side filters in `ON` when you need outer semantics.
* Joining on non-unique keys multiplies rows (fan-out) and inflates aggregates.
* `SELECT *` with joins returns duplicate column names and wide rows.
* NULL-safe equality differs by dialect (`IS NOT DISTINCT FROM` in Postgres).

## 🔗 Related [#-related]

* [Select](/docs/sql/select)
* [Indexes](/docs/sql/indexes)
* [Insert / Update / Delete](/docs/sql/insert-update-delete)
* [Transactions](/docs/sql/transactions)


---

# Normalization (/docs/sql/normalization)



# Normalization [#normalization]

*SQL · Reference cheat sheet*

***

## 📋 Overview [#-overview]

Normalization structures relational schemas to reduce redundancy and update anomalies (1NF → 2NF → 3NF / BCNF). Practical designs often normalize OLTP schemas and selectively denormalize for read-heavy analytics with clear sync rules.

## 🔧 Core concepts [#-core-concepts]

| Form        | Rule of thumb                                  |
| ----------- | ---------------------------------------------- |
| 1NF         | Atomic cells; no repeating groups              |
| 2NF         | 1NF + no partial dependency on a composite key |
| 3NF         | 2NF + no transitive dependency on non-keys     |
| BCNF        | Every determinant is a candidate key           |
| Denormalize | Controlled copies for performance / UX         |

* **Anomalies** — insert/update/delete anomalies from duplicated facts.
* **Keys** — primary, candidate, foreign keys define dependencies.
* **Join cost** — more normalization → more joins; index FKs.

## 💡 Examples [#-examples]

```plaintext
Bad (unnormalized orders):
order_id | customer_name | customer_email | item1 | item2

Better:
customers(id, name, email)
orders(id, customer_id, created_at)
order_items(order_id, product_id, qty, price_cents)
products(id, sku, title)
```

```sql
-- 3NF-ish: status lookup instead of repeating labels everywhere
CREATE TABLE order_statuses (
  id TEXT PRIMARY KEY,          -- 'paid', 'shipped'
  label TEXT NOT NULL
);

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(id),
  status_id TEXT NOT NULL REFERENCES order_statuses(id)
);
```

## ⚠️ Pitfalls [#️-pitfalls]

* Over-normalization can make simple reads painful — balance with access patterns.
* Denormalized caches drift without triggers/jobs/events to refresh them.
* Storing JSON blobs for “flexibility” often recreates 1NF violations inside documents.
* Composite keys without care lead to 2NF violations (attributes of only part of the key).
* Surrogate keys don’t remove the need for unique business keys (`UNIQUE (sku)`).

## 🔗 Related [#-related]

* [foreign\_keys.md](/docs/sql/foreign-keys)
* [constraints.md](/docs/sql/constraints)
* [schemas.md](/docs/sql/schemas)
* [data\_types.md](/docs/sql/data-types)
* [joins.md](/docs/sql/joins)
* [create\_alter\_drop.md](/docs/sql/create-alter-drop)


---

# NULL Basics (/docs/sql/null-basics)



# NULL Basics [#null-basics]

*SQL · Reference cheat sheet*

***

## 📋 Overview [#-overview]

`NULL` means **unknown / missing**, not the number zero and not an empty string. SQL uses three-valued logic (`TRUE`, `FALSE`, `UNKNOWN`), so comparisons with `NULL` need special operators.

## 🔧 Core concepts [#-core-concepts]

| Idea                      | Detail                    |
| ------------------------- | ------------------------- |
| `NULL`                    | Missing value             |
| `IS NULL` / `IS NOT NULL` | Correct null tests        |
| `=` with NULL             | Yields UNKNOWN, not TRUE  |
| `COALESCE(a, b)`          | First non-null among args |
| `NOT NULL` column         | Disallow missing values   |

Aggregates like `COUNT(column)` ignore nulls; `COUNT(*)` counts rows.

## 💡 Examples [#-examples]

**Insert and test nulls:**

```sql
CREATE TABLE tasks (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  due_date TEXT
);

INSERT INTO tasks (title, due_date) VALUES
  ('Write notes', '2026-07-12'),
  ('Review PR', NULL);

SELECT * FROM tasks WHERE due_date IS NULL;
SELECT * FROM tasks WHERE due_date IS NOT NULL;
```

**Why `=` fails:**

```sql
-- Usually returns no rows, even if due_date is NULL
SELECT * FROM tasks WHERE due_date = NULL;
```

**COALESCE for defaults:**

```sql
SELECT
  title,
  COALESCE(due_date, 'No due date') AS due_label
FROM tasks;
```

**Counts:**

```sql
SELECT COUNT(*) AS all_rows,
       COUNT(due_date) AS with_due_date
FROM tasks;
```

## ⚠️ Pitfalls [#️-pitfalls]

* `NULL = NULL` is not true — use `IS NULL`.
* Empty string `''` is not NULL (unless your app treats them the same).
* `NOT IN (SELECT ...)` with nulls in the list can surprise you.
* Nullable foreign keys / optional fields are fine — document the meaning of “missing.”

## 🔗 Related [#-related]

* [tables\_basics.md](/docs/sql/tables-basics)
* [primary\_keys\_basics.md](/docs/sql/primary-keys-basics)
* [nulls.md](/docs/sql/nulls)
* [getting\_started.md](/docs/sql/getting-started)


---

# NULLs (/docs/sql/nulls)



# NULLs [#nulls]

*SQL · Reference cheat sheet*

***

## 📋 Overview [#-overview]

`NULL` means unknown/missing. Any comparison with `NULL` yields unknown (not true), so filters need `IS NULL` / `IS NOT NULL`. Aggregates, unique constraints, and outer joins all have NULL-specific behavior.

## 🔧 Core concepts [#-core-concepts]

* **Three-valued logic** — TRUE / FALSE / UNKNOWN.
* **IS NULL** — only correct NULL test; `= NULL` never matches.
* **COALESCE / NULLIF** — substitute or nullify values.
* **Aggregates** — ignore NULLs (except `COUNT(*)`).
* **Outer joins** — non-matching side fills NULLs.
* **DISTINCT / UNIQUE** — NULL treatment differs by dialect.

## 💡 Examples [#-examples]

```sql
SELECT * FROM users WHERE deleted_at IS NULL;
SELECT * FROM users WHERE deleted_at IS NOT NULL;

SELECT COALESCE(display_name, email, 'unknown') AS label FROM users;
SELECT NULLIF(trim(name), '') AS name FROM users;  -- '' → NULL

SELECT COUNT(*) AS rows, COUNT(phone) AS with_phone FROM users;

-- NULL-safe equality (Postgres IS NOT DISTINCT FROM)
SELECT * FROM t WHERE a IS NOT DISTINCT FROM b;
-- MySQL: <=> null-safe equals

-- Sort NULLs
SELECT * FROM products ORDER BY rank NULLS LAST;     -- Postgres
-- MySQL: ORDER BY rank IS NULL, rank
```

## ⚠️ Pitfalls [#️-pitfalls]

* `NOT IN (1, 2, NULL)` never returns true — prefer `NOT EXISTS`.
* `UNIQUE` allowing multiple NULLs (Postgres) surprises people from other DBs.
* `OR col = NULL` is always unknown — use `IS NULL`.
* Expressions like `price + tax` become NULL if either is NULL — `COALESCE` to 0 when appropriate.
* CHECK constraints: `CHECK (price > 0)` allows NULL prices unless `NOT NULL`.

## 🔗 Related [#-related]

* [where\_having.md](/docs/sql/where-having)
* [constraints.md](/docs/sql/constraints)
* [aggregate.md](/docs/sql/aggregate)
* [joins.md](/docs/sql/joins)
* [order\_limit.md](/docs/sql/order-limit)
* [distinct\_group\_by.md](/docs/sql/distinct-group-by)


---

# ORDER BY and LIMIT (/docs/sql/order-limit)



# ORDER BY and LIMIT [#order-by-and-limit]

*SQL · Reference cheat sheet*

***

## 📋 Overview [#-overview]

`ORDER BY` sorts result rows; `LIMIT`/`OFFSET` (or `FETCH FIRST` / `TOP`) page results. Stable pagination needs a deterministic order key. Dialects differ: Postgres/`MySQL` use `LIMIT`; SQL Server uses `TOP`/`OFFSET FETCH`.

## 🔧 Core concepts [#-core-concepts]

* **Sort keys** — columns or expressions; `ASC`/`DESC`; multi-key sorts.
* **NULL order** — Postgres `NULLS FIRST|LAST`; MySQL NULLs first in ASC by default.
* **LIMIT / OFFSET** — `LIMIT count OFFSET skip` (MySQL/Postgres).
* **FETCH** — standard: `OFFSET n ROWS FETCH NEXT m ROWS ONLY`.
* **Determinism** — add unique tie-breaker (e.g. `id`) for stable pages.

## 💡 Examples [#-examples]

```sql
SELECT id, email, created_at
FROM users
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 40;

-- Keyset pagination (preferred over deep OFFSET)
SELECT id, email, created_at
FROM users
WHERE (created_at, id) < ('2026-07-01 12:00:00+00', 12345)
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Standard FETCH (Postgres)
SELECT * FROM products
ORDER BY price
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

-- MySQL
SELECT * FROM products ORDER BY price LIMIT 10 OFFSET 0;
```

## ⚠️ Pitfalls [#️-pitfalls]

* Large `OFFSET` is slow — prefer keyset/seek pagination.
* `ORDER BY` without index can sort on disk — check `EXPLAIN`.
* Non-deterministic order without unique keys → duplicate/missing rows across pages.
* Selecting `*` with random order (`ORDER BY random()`) is expensive.
* `LIMIT` without `ORDER BY` returns an arbitrary set.

## 🔗 Related [#-related]

* [select.md](/docs/sql/select)
* [nulls.md](/docs/sql/nulls)
* [indexes.md](/docs/sql/indexes)
* [window\_functions.md](/docs/sql/window-functions)
* [explain\_analyze.md](/docs/sql/explain-analyze)
* [distinct\_group\_by.md](/docs/sql/distinct-group-by)


---

# Primary Keys Basics (/docs/sql/primary-keys-basics)



# Primary Keys Basics [#primary-keys-basics]

*SQL · Reference cheat sheet*

***

## 📋 Overview [#-overview]

A &#x2A;*primary key (PK)** uniquely identifies each row in a table. Most tables use a single column (often `id`). Databases enforce uniqueness and non-null for primary keys.

## 🔧 Core concepts [#-core-concepts]

| Idea           | Meaning                                               |
| -------------- | ----------------------------------------------------- |
| Uniqueness     | No two rows share the same PK value                   |
| Not null       | PK columns cannot be NULL                             |
| Surrogate key  | Artificial `id` (common)                              |
| Natural key    | Real-world unique value (email, ISBN) — use carefully |
| Auto-increment | DB generates the next id                              |
| Composite key  | PK made of multiple columns                           |

Foreign keys in other tables often reference this primary key.

## 💡 Examples [#-examples]

**Integer primary key (SQLite style):**

```sql
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL
);

INSERT INTO users (email, name) VALUES
  ('ada@example.com', 'Ada'),
  ('grace@example.com', 'Grace');

SELECT id, email FROM users;
```

**Explicit ids:**

```sql
INSERT INTO users (id, email, name)
VALUES (100, 'linus@example.com', 'Linus');
```

**Composite primary key:**

```sql
CREATE TABLE enrollment (
  student_id INTEGER NOT NULL,
  course_id INTEGER NOT NULL,
  enrolled_on TEXT NOT NULL,
  PRIMARY KEY (student_id, course_id)
);

INSERT INTO enrollment VALUES (1, 10, '2026-01-15');
-- INSERT INTO enrollment VALUES (1, 10, '2026-02-01'); -- would fail
```

**Lookup by primary key:**

```sql
SELECT name FROM users WHERE id = 1;
```

## ⚠️ Pitfalls [#️-pitfalls]

* Changing primary key values later breaks references — treat ids as stable.
* Using mutable natural keys (email) as PK causes pain when they change.
* Forgetting a PK makes updates/deletes harder and invites duplicate rows.
* Auto-increment behavior and syntax differ (`SERIAL`, `IDENTITY`, `AUTO_INCREMENT`).

## 🔗 Related [#-related]

* [tables\_basics.md](/docs/sql/tables-basics)
* [null\_basics.md](/docs/sql/null-basics)
* [foreign\_keys.md](/docs/sql/foreign-keys)
* [constraints.md](/docs/sql/constraints)


---

# Schemas (/docs/sql/schemas)



# Schemas [#schemas]

*SQL · Reference cheat sheet*

***

## 📋 Overview [#-overview]

Schemas namespace database objects (tables, views, functions). Postgres uses first-class schemas (`public`, custom); MySQL often treats database = schema. Use schemas to separate domains, tenants (carefully), or extension objects.

## 🔧 Core concepts [#-core-concepts]

* **Qualified names** — `schema.table` (Postgres); `db.table` (MySQL).
* **Search path** — Postgres `search_path` resolves unqualified names.
* **CREATE SCHEMA** — logical grouping; permissions per schema.
* **Ownership** — objects inherit owner; grants are schema + object level.
* **Extensions** — Postgres often installs into dedicated schemas.

## 💡 Examples [#-examples]

```sql
-- Postgres
CREATE SCHEMA app AUTHORIZATION app_user;
CREATE SCHEMA analytics;

CREATE TABLE app.users (
  id BIGSERIAL PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

CREATE TABLE analytics.daily_active AS
SELECT CURRENT_DATE AS day, COUNT(*) AS n FROM app.users;

SET search_path TO app, public;
SELECT * FROM users;  -- resolves to app.users

GRANT USAGE ON SCHEMA app TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO readonly;

-- MySQL: database as schema
CREATE DATABASE app CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE app;
CREATE TABLE users (id BIGINT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(255) NOT NULL);
```

## ⚠️ Pitfalls [#️-pitfalls]

* Unqualified names + surprising `search_path` → wrong table (security risk with `SECURITY DEFINER`).
* Cross-schema FKs need privileges on both schemas.
* Dump/restore tools must include non-`public` schemas explicitly.
* MySQL “schema” wording ≠ Postgres schemas — don’t assume identical DDL.
* Overusing schemas for multi-tenant isolation is harder than row-level tenancy for many apps.

## 🔗 Related [#-related]

* [create\_alter\_drop.md](/docs/sql/create-alter-drop)
* [views.md](/docs/sql/views)
* [stored\_procedures.md](/docs/sql/stored-procedures)
* [permissions via constraints.md](/docs/sql/constraints)
* [normalization.md](/docs/sql/normalization)
* [data\_types.md](/docs/sql/data-types)


---

# SELECT (/docs/sql/select)



# SELECT [#select]

**SQL · Reference cheat sheet**

***

## 📖 Overview [#-overview]

`SELECT` retrieves rows from one or more tables. Shape results with column lists, filters (`WHERE`), ordering, limits, and aggregates. Prefer explicit column names over `SELECT *` in application queries.

## 🧩 Core concepts [#-core-concepts]

* **Projection** — choose columns, aliases, expressions.
* **Filtering** — `WHERE` (row-level) vs `HAVING` (after `GROUP BY`).
* **Sorting / paging** — `ORDER BY`, `LIMIT`/`OFFSET` (or `FETCH FIRST` / `TOP`).
* **Aggregates** — `COUNT`, `SUM`, `AVG`, `MIN`, `MAX` with `GROUP BY`.
* **DISTINCT** — deduplicate result rows.
* **Subqueries / CTEs** — nest logic with `(SELECT …)` or `WITH … AS`.

## 💡 Examples [#-examples]

```sql
-- Basic
SELECT id, email, created_at
FROM users
WHERE active = TRUE
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;

-- Aliases & expressions
SELECT
  first_name || ' ' || last_name AS full_name,
  DATE_PART('year', AGE(born_at)) AS age
FROM people;

-- Aggregates
SELECT status, COUNT(*) AS total
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY status
HAVING COUNT(*) > 10
ORDER BY total DESC;

-- CTE
WITH recent AS (
  SELECT user_id, COUNT(*) AS orders
  FROM orders
  WHERE created_at >= NOW() - INTERVAL '7 days'
  GROUP BY user_id
)
SELECT u.email, r.orders
FROM recent r
JOIN users u ON u.id = r.user_id
ORDER BY r.orders DESC;
```

## ⚠️ Pitfalls [#️-pitfalls]

* `SELECT *` breaks when schemas change and transfers unused columns.
* Filtering on expressions (`WHERE YEAR(created_at) = 2026`) can prevent index use — prefer range predicates.
* `NULL` never equals anything — use `IS NULL` / `IS DISTINCT FROM`.
* Dialect differences: `LIMIT` (Postgres/MySQL) vs `TOP` (SQL Server) vs `FETCH` (standard).

## 🔗 Related [#-related]

* [Insert / Update / Delete](/docs/sql/insert-update-delete)
* [Joins](/docs/sql/joins)
* [Indexes](/docs/sql/indexes)
* [Transactions](/docs/sql/transactions)


---

# Stored procedures and functions (/docs/sql/stored-procedures)



# Stored procedures and functions [#stored-procedures-and-functions]

*SQL · Reference cheat sheet*

***

## 📋 Overview [#-overview]

Stored procedures/functions encapsulate SQL and procedural logic in the database. Postgres distinguishes functions (`RETURNS`) and procedures (`CALL`, transaction control). MySQL uses `PROCEDURE` / `FUNCTION` with different syntax. Prefer app-layer services unless you need DB-local batching or security definer patterns.

## 🔧 Core concepts [#-core-concepts]

* **Function** — returns a value/table; usable in SQL expressions (with limits).
* **Procedure** — invoked with `CALL`; may manage transactions (Postgres).
* **Languages** — SQL, PL/pgSQL (PG), MySQL stored program language.
* **Volatility** — `IMMUTABLE` / `STABLE` / `VOLATILE` (Postgres optimizer).
* **Security** — `SECURITY DEFINER` vs invoker rights (careful with privileges).

## 💡 Examples [#-examples]

```sql
-- Postgres function
CREATE OR REPLACE FUNCTION user_order_count(p_user_id BIGINT)
RETURNS BIGINT
LANGUAGE sql
STABLE
AS $$
  SELECT COUNT(*) FROM orders WHERE user_id = p_user_id;
$$;

SELECT email, user_order_count(id) FROM users;

-- Postgres procedure
CREATE OR REPLACE PROCEDURE archive_old_orders(p_days INT)
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO orders_archive
  SELECT * FROM orders
  WHERE created_at < NOW() - (p_days || ' days')::INTERVAL;
  DELETE FROM orders
  WHERE created_at < NOW() - (p_days || ' days')::INTERVAL;
  COMMIT;  -- procedures can commit (PG)
END;
$$;

CALL archive_old_orders(365);
```

```sql
-- MySQL procedure sketch
DELIMITER //
CREATE PROCEDURE archive_old_orders(IN p_days INT)
BEGIN
  DELETE FROM orders WHERE created_at < NOW() - INTERVAL p_days DAY;
END //
DELIMITER ;
```

## ⚠️ Pitfalls [#️-pitfalls]

* Business logic in the DB is harder to test/version than app code.
* `SECURITY DEFINER` can escalate privileges — lock search\_path and owners.
* MySQL `DELIMITER` is a client concept — needed for multi-statement bodies.
* Functions with side effects in `SELECT` surprise readers — mark volatility correctly.
* Portability between Postgres and MySQL stored programs is poor.

## 🔗 Related [#-related]

* [triggers.md](/docs/sql/triggers)
* [transactions.md](/docs/sql/transactions)
* [functions\_builtin.md](/docs/sql/functions-builtin)
* [schemas.md](/docs/sql/schemas)
* [insert\_update\_delete.md](/docs/sql/insert-update-delete)
* [explain\_analyze.md](/docs/sql/explain-analyze)


---

# Subqueries (/docs/sql/subquery)



# Subqueries [#subqueries]

*SQL · Reference cheat sheet*

***

## 📋 Overview [#-overview]

Subqueries are nested `SELECT`s in `WHERE`, `FROM`, `SELECT`, or DML. Prefer joins or CTEs for readability when equivalent; use subqueries for existence checks, scalar lookups, and derived tables.

## 🔧 Core concepts [#-core-concepts]

* **Scalar** — returns one value; usable in expressions.
* **IN / NOT IN** — membership; watch NULLs with `NOT IN`.
* **EXISTS / NOT EXISTS** — semi-join; often best for existence.
* **Derived table** — subquery in `FROM` (must be aliased).
* **Correlated** — references outer row; runs per outer row (optimizer may rewrite).
* **LATERAL** — Postgres: correlated FROM subquery.

## 💡 Examples [#-examples]

```sql
-- Scalar
SELECT name,
       (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;

-- EXISTS
SELECT u.id, u.email
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id AND o.total > 100
);

-- IN
SELECT * FROM products
WHERE category_id IN (SELECT id FROM categories WHERE active);

-- Derived table
SELECT c.customer_id, c.revenue
FROM (
  SELECT customer_id, SUM(total) AS revenue
  FROM orders
  GROUP BY customer_id
) AS c
WHERE c.revenue > 1000;

-- Postgres LATERAL
SELECT u.id, o.id AS last_order_id
FROM users u
LEFT JOIN LATERAL (
  SELECT id FROM orders
  WHERE user_id = u.id
  ORDER BY created_at DESC
  LIMIT 1
) o ON TRUE;
```

## ⚠️ Pitfalls [#️-pitfalls]

* `NOT IN (SELECT col …)` yields unknown if the subquery returns NULL — prefer `NOT EXISTS`.
* Correlated subqueries can be slow without indexes on join keys.
* Scalar subquery that returns >1 row errors at runtime.
* MySQL historically limited subquery materialization — check `EXPLAIN`.
* Deep nesting hurts readability — use CTEs.

## 🔗 Related [#-related]

* [cte.md](/docs/sql/cte)
* [joins.md](/docs/sql/joins)
* [where\_having.md](/docs/sql/where-having)
* [select.md](/docs/sql/select)
* [explain\_analyze.md](/docs/sql/explain-analyze)
* [aggregate.md](/docs/sql/aggregate)


---

# Tables Basics (/docs/sql/tables-basics)



# Tables Basics [#tables-basics]

*SQL · Reference cheat sheet*

***

## 📋 Overview [#-overview]

A **table** stores structured data in columns (schema) and rows (data). You define column names and types with `CREATE TABLE`, then add rows with `INSERT` and read them with `SELECT`.

## 🔧 Core concepts [#-core-concepts]

| Idea         | Meaning                                         |
| ------------ | ----------------------------------------------- |
| Column type  | `INTEGER`, `TEXT`, `REAL`, `BOOLEAN`, `DATE`, … |
| `NOT NULL`   | Column must have a value                        |
| `DEFAULT`    | Value used when insert omits the column         |
| `INSERT`     | Add row(s)                                      |
| `SELECT`     | Read row(s)                                     |
| `DROP TABLE` | Delete the table (destructive)                  |

Design tables around one clear entity (users, orders, products).

## 💡 Examples [#-examples]

**Create and inspect:**

```sql
CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  pages INTEGER,
  read INTEGER DEFAULT 0
);

INSERT INTO books (title, pages) VALUES
  ('Dune', 412),
  ('Foundation', 255);

SELECT * FROM books;
```

**Select specific columns:**

```sql
SELECT title, pages
FROM books
ORDER BY pages DESC;
```

**Add a column later (shape varies by dialect):**

```sql
ALTER TABLE books ADD COLUMN author TEXT;
UPDATE books SET author = 'Unknown' WHERE author IS NULL;
SELECT title, author FROM books;
```

**Remove a practice table:**

```sql
DROP TABLE IF EXISTS books;
```

## ⚠️ Pitfalls [#️-pitfalls]

* `DROP TABLE` deletes data permanently (unless you have backups).
* Types differ by database — check docs for Postgres vs SQLite vs MySQL.
* Wide “god tables” with dozens of unrelated columns become hard to query.
* Omitting `NOT NULL` on required fields allows incomplete rows.

## 🔗 Related [#-related]

* [getting\_started.md](/docs/sql/getting-started)
* [hello\_world.md](/docs/sql/hello-world)
* [primary\_keys\_basics.md](/docs/sql/primary-keys-basics)
* [null\_basics.md](/docs/sql/null-basics)


---

# Transactions (/docs/sql/transactions)



# Transactions [#transactions]

**SQL · Reference cheat sheet**

***

## 📖 Overview [#-overview]

Transactions group statements into an atomic unit: all commit or all roll back. Isolation levels control what concurrent sessions can see. Use them for multi-step writes that must stay consistent.

## 🧩 Core concepts [#-core-concepts]

* **ACID** — Atomicity, Consistency, Isolation, Durability.
* **BEGIN / COMMIT / ROLLBACK** — start, persist, or undo a unit of work.
* **Savepoints** — partial rollback inside a transaction.
* **Isolation levels** — `READ UNCOMMITTED`, `READ COMMITTED`, `REPEATABLE READ`, `SERIALIZABLE`.
* **Locks** — row/table locks and deadlocks under contention.
* **Autocommit** — many clients commit each statement unless a transaction is open.

## 💡 Examples [#-examples]

```sql
BEGIN;

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

-- Optional check
SELECT balance FROM accounts WHERE id IN (1, 2);

COMMIT;
-- or: ROLLBACK;

-- Savepoint
BEGIN;
INSERT INTO orders (user_id, total) VALUES (9, 50) RETURNING id;
SAVEPOINT sp_items;
INSERT INTO order_items (order_id, product_id, qty) VALUES (currval('orders_id_seq'), 55, 1);
-- on failure:
ROLLBACK TO SAVEPOINT sp_items;
COMMIT;

-- Isolation (PostgreSQL example)
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- critical reads/writes
COMMIT;
```

Application pattern (pseudocode):

```plaintext
BEGIN
  write A
  write B
COMMIT  -- or ROLLBACK on error
```

## ⚠️ Pitfalls [#️-pitfalls]

* Long transactions hold locks and inflate MVCC bloat — keep them short.
* Ignoring errors and still committing can persist partial logical work if statements were autocommitted.
* Deadlocks: engines abort one session — retry the whole transaction.
* Isolation defaults differ (e.g. Postgres `READ COMMITTED` vs MySQL InnoDB `REPEATABLE READ`) — don’t assume anomaly behavior.

## 🔗 Related [#-related]

* [Insert / Update / Delete](/docs/sql/insert-update-delete)
* [Select](/docs/sql/select)
* [Indexes](/docs/sql/indexes)
* [Joins](/docs/sql/joins)


---

# Triggers (/docs/sql/triggers)



# Triggers [#triggers]

*SQL · Reference cheat sheet*

***

## 📋 Overview [#-overview]

Triggers run procedural logic on `INSERT`/`UPDATE`/`DELETE` (and some DDL). Use sparingly for auditing, derived columns, or enforcing rules that constraints can’t express. Prefer application transactions + constraints when possible — triggers are easy to overlook.

## 🔧 Core concepts [#-core-concepts]

* **Timing** — `BEFORE` / `AFTER` (Postgres also `INSTEAD OF` on views).
* **Granularity** — `FOR EACH ROW` vs `FOR EACH STATEMENT`.
* **Transition rows** — `NEW` / `OLD` (row triggers).
* **Function body** — Postgres: trigger functions in PL/pgSQL; MySQL: inline `BEGIN … END`.
* **When** — optional `WHEN (condition)` (Postgres).

## 💡 Examples [#-examples]

```sql
-- Postgres
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at := NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_users_updated
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();  -- PG14+: EXECUTE FUNCTION; older: EXECUTE PROCEDURE

-- Audit log
CREATE TRIGGER trg_orders_audit
AFTER UPDATE ON orders
FOR EACH ROW
WHEN (OLD.status IS DISTINCT FROM NEW.status)
EXECUTE FUNCTION log_order_status_change();
```

```sql
-- MySQL
CREATE TRIGGER trg_users_updated
BEFORE UPDATE ON users
FOR EACH ROW
SET NEW.updated_at = CURRENT_TIMESTAMP;
```

## ⚠️ Pitfalls [#️-pitfalls]

* Hidden side effects surprise developers — document triggers next to the table.
* Mutating the same table in a row trigger can cause recursion / errors.
* Statement vs row triggers fire differently for multi-row SQL.
* Disabling triggers for bulk loads can leave data inconsistent if forgotten.
* Replication / logical decoding may need trigger design awareness.

## 🔗 Related [#-related]

* [stored\_procedures.md](/docs/sql/stored-procedures)
* [constraints.md](/docs/sql/constraints)
* [transactions.md](/docs/sql/transactions)
* [create\_alter\_drop.md](/docs/sql/create-alter-drop)
* [insert\_update\_delete.md](/docs/sql/insert-update-delete)
* [foreign\_keys.md](/docs/sql/foreign-keys)


---

# UPSERT (/docs/sql/upsert)



# UPSERT [#upsert]

*SQL · Reference cheat sheet*

***

## 📋 Overview [#-overview]

UPSERT inserts a row or updates it when a conflict occurs (unique/PK violation). Postgres uses `INSERT … ON CONFLICT`; MySQL uses `ON DUPLICATE KEY UPDATE` or `REPLACE`. Choose conflict targets carefully to avoid silent wrong updates.

## 🔧 Core concepts [#-core-concepts]

* **Conflict target** — constraint name or column list that defines uniqueness.
* **DO NOTHING / DO UPDATE** — ignore vs merge (Postgres).
* **Excluded row** — Postgres `EXCLUDED.col` is the would-be insert.
* **MySQL** — `VALUES(col)` (older) / aliases (8.0.19+) for new values.
* **Idempotency** — natural keys + upsert for retries.

## 💡 Examples [#-examples]

```sql
-- Postgres
INSERT INTO users (email, name)
VALUES ('ada@example.com', 'Ada')
ON CONFLICT (email)
DO UPDATE SET
  name = EXCLUDED.name,
  updated_at = NOW()
RETURNING id, email;

INSERT INTO hits (slug, n)
VALUES ('home', 1)
ON CONFLICT (slug)
DO UPDATE SET n = hits.n + 1;

INSERT INTO users (email, name)
VALUES ('ada@example.com', 'Ada')
ON CONFLICT DO NOTHING;
```

```sql
-- MySQL
INSERT INTO users (email, name)
VALUES ('ada@example.com', 'Ada')
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  updated_at = CURRENT_TIMESTAMP;

-- REPLACE INTO deletes then inserts (side effects on FKs/AI) — prefer ODKu
```

## ⚠️ Pitfalls [#️-pitfalls]

* Upsert updates only on the conflict you specify — wrong unique key → duplicate rows.
* `REPLACE INTO` (MySQL) deletes the old row → FK cascades / new auto-increment.
* Partial unique indexes (Postgres) need matching `ON CONFLICT` inference.
* Concurrent upserts need proper unique constraints or you’ll still race.
* Don’t use upsert to hide missing `WHERE` on updates — be explicit.

## 🔗 Related [#-related]

* [insert\_update\_delete.md](/docs/sql/insert-update-delete)
* [constraints.md](/docs/sql/constraints)
* [transactions.md](/docs/sql/transactions)
* [indexes.md](/docs/sql/indexes)
* [nulls.md](/docs/sql/nulls)
* [foreign\_keys.md](/docs/sql/foreign-keys)


---

# Views (/docs/sql/views)



# Views [#views]

*SQL · Reference cheat sheet*

***

## 📋 Overview [#-overview]

Views are stored queries that act like virtual tables. Use them for reusable projections, security (column/row hiding), and simplifying joins. Materialized views store results physically (Postgres); MySQL has views but not native materialized views.

## 🔧 Core concepts [#-core-concepts]

* **CREATE VIEW** — non-materialized; runs underlying query on read.
* **REPLACE / OR REPLACE** — update definition.
* **Updatable views** — limited; simple views may allow `INSERT`/`UPDATE`.
* **Materialized** — Postgres `MATERIALIZED VIEW` + `REFRESH`.
* **Security** — grant on view without granting base tables (with caveats).

## 💡 Examples [#-examples]

```sql
CREATE VIEW active_users AS
SELECT id, email, created_at
FROM users
WHERE deleted_at IS NULL;

CREATE OR REPLACE VIEW order_summaries AS
SELECT o.id, u.email, o.total, o.status
FROM orders o
JOIN users u ON u.id = o.user_id;

SELECT * FROM active_users WHERE created_at > CURRENT_DATE - INTERVAL '7 days';

-- Postgres materialized
CREATE MATERIALIZED VIEW daily_revenue AS
SELECT DATE(created_at) AS day, SUM(total) AS revenue
FROM orders
GROUP BY DATE(created_at)
WITH DATA;

REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;  -- needs unique index

DROP VIEW IF EXISTS active_users;
```

## ⚠️ Pitfalls [#️-pitfalls]

* Views don’t automatically speed queries — they can hide expensive joins.
* Nested views make `EXPLAIN` harder — flatten when debugging.
* `OR REPLACE` can break consumers if column names/types change.
* Materialized views go stale until refreshed — schedule carefully.
* MySQL: no `MATERIALIZED VIEW`; emulate with tables + jobs.

## 🔗 Related [#-related]

* [select.md](/docs/sql/select)
* [cte.md](/docs/sql/cte)
* [joins.md](/docs/sql/joins)
* [schemas.md](/docs/sql/schemas)
* [create\_alter\_drop.md](/docs/sql/create-alter-drop)
* [explain\_analyze.md](/docs/sql/explain-analyze)


---

# WHERE and HAVING (/docs/sql/where-having)



# WHERE and HAVING [#where-and-having]

*SQL · Reference cheat sheet*

***

## 📋 Overview [#-overview]

`WHERE` filters rows before aggregation; `HAVING` filters groups after `GROUP BY`. Use `WHERE` for row predicates and `HAVING` for aggregate conditions (`COUNT`, `SUM`, …).

## 🔧 Core concepts [#-core-concepts]

* **Order** — `FROM` → `WHERE` → `GROUP BY` → `HAVING` → `SELECT` → `ORDER BY`.
* **Predicates** — `=`, `&lt;>`, `&lt;`, `IN`, `BETWEEN`, `LIKE`/`ILIKE`, `IS NULL`.
* **Boolean** — `AND` / `OR` / `NOT`; parentheses for clarity.
* **Aliases** — `HAVING` may allow select aliases in some engines; don’t rely on it portably.
* **Sargability** — avoid wrapping indexed columns in functions in `WHERE`.

## 💡 Examples [#-examples]

```sql
SELECT id, email
FROM users
WHERE is_active = TRUE
  AND created_at >= DATE '2026-01-01'
  AND email LIKE '%@example.com';

SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS revenue
FROM orders
WHERE status <> 'cancelled'
GROUP BY customer_id
HAVING COUNT(*) >= 3
   AND SUM(total) > 1000;

-- Prefer WHERE over HAVING when possible
SELECT customer_id, COUNT(*) AS c
FROM orders
WHERE region = 'EU'          -- row filter
GROUP BY customer_id
HAVING COUNT(*) >= 3;        -- group filter
```

```sql
-- NULL-safe checks
WHERE deleted_at IS NULL
-- NOT: WHERE deleted_at = NULL
```

## ⚠️ Pitfalls [#️-pitfalls]

* `WHERE COUNT(*) > 1` is invalid — aggregates belong in `HAVING` (or subquery).
* `OR` across columns often disables simple index use — consider `UNION`.
* `WHERE col = NULL` never matches — use `IS NULL`.
* MySQL `ONLY_FULL_GROUP_BY` rejects non-aggregated selected columns.
* Filtering on `SELECT` aliases in `WHERE` is invalid in standard SQL.

## 🔗 Related [#-related]

* [select.md](/docs/sql/select)
* [aggregate.md](/docs/sql/aggregate)
* [distinct\_group\_by.md](/docs/sql/distinct-group-by)
* [nulls.md](/docs/sql/nulls)
* [order\_limit.md](/docs/sql/order-limit)
* [subquery.md](/docs/sql/subquery)


---

# Window functions (/docs/sql/window-functions)



# Window functions [#window-functions]

*SQL · Reference cheat sheet*

***

## 📋 Overview [#-overview]

Window functions compute values across related rows without collapsing them (`OVER (…)`) — ranking, running totals, lead/lag. Supported in Postgres and MySQL 8+. Prefer windows over self-joins for analytics.

## 🔧 Core concepts [#-core-concepts]

* **OVER** — `PARTITION BY`, `ORDER BY`, frame (`ROWS` / `RANGE`).
* **Ranking** — `ROW_NUMBER`, `RANK`, `DENSE_RANK`, `NTILE`.
* **Offset** — `LAG` / `LEAD`, `FIRST_VALUE` / `LAST_VALUE`.
* **Aggregates as windows** — `SUM(x) OVER (ORDER BY …)`.
* **Frame defaults** — with `ORDER BY`, default often `RANGE UNBOUNDED PRECEDING` → current row.

## 💡 Examples [#-examples]

```sql
SELECT
  customer_id,
  created_at,
  total,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at) AS rn,
  SUM(total) OVER (
    PARTITION BY customer_id
    ORDER BY created_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total,
  LAG(total) OVER (PARTITION BY customer_id ORDER BY created_at) AS prev_total
FROM orders;

-- Top-N per group
SELECT * FROM (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY score DESC) AS rn
  FROM products
) t
WHERE rn <= 3;
```

```sql
-- Percent of total
SELECT
  sku,
  revenue,
  revenue / SUM(revenue) OVER () AS share
FROM sku_revenue;
```

## ⚠️ Pitfalls [#️-pitfalls]

* Forgetting frame clauses makes `LAST_VALUE` surprising — set explicit frames.
* `RANK` leaves gaps; `DENSE_RANK` does not; `ROW_NUMBER` never ties.
* Windows can’t appear directly in `WHERE` — wrap in subquery/CTE.
* Large partitions + sorts are memory/disk heavy — index partition/order keys.
* MySQL \< 8 lacks window functions.

## 🔗 Related [#-related]

* [aggregate.md](/docs/sql/aggregate)
* [cte.md](/docs/sql/cte)
* [order\_limit.md](/docs/sql/order-limit)
* [select.md](/docs/sql/select)
* [subquery.md](/docs/sql/subquery)
* [explain\_analyze.md](/docs/sql/explain-analyze)