SQL Fundamentals¶

Database diagram¶

Every example on this page queries the same toy schema. Read it once, refer back as needed.

erDiagram
  USERS ||--o{ ORDERS : places

  USERS {
    int id PK
    string name
    string country
    string email
    string phone
    decimal lifetime_value
    timestamp created_at
  }
  ORDERS {
    int id PK
    int user_id FK
    string status
    decimal total
    timestamp created_at
  }
  LEADS {
    int id PK
    string email
    timestamp created_at
  }
  EMPLOYEES {
    int id PK
    string name
    int manager_id FK
  }

How to read this diagram¶

This is an ER (entity-relationship) diagram in Mermaid syntax. Conventions:

• Each box is a table. Bold name on top; columns listed below as type name [PK|FK].
• PK = primary key. Uniquely identifies a row — no two rows share the same PK value.
• FK = foreign key. Column whose value is the PK of another table — the "join key" that says which row this one belongs to.

• Line between two tables = a relationship. The endpoint markers are crow's-foot notation:

  • || exactly one
  • o| zero or one
  • }o zero or many
  • }| one or many

  So USERS ||--o{ ORDERS : places reads "one user places zero-or-many orders." - Self-loop (EMPLOYEES }o--o| EMPLOYEES : reports_to) is a relationship into the same table — many employees report to zero-or-one manager (the CEO has none). Standard pattern for trees/hierarchies (employees ↔ manager, comments ↔ parent, categories ↔ subcategory). - No line ≠ unrelated. USERS.email and LEADS.email aren't formally linked, but you can still match them as a set operation.

You'll see this schema referenced throughout the rest of the page.

Order of operations¶

SQL has two orders — the order you write a query and the order the engine executes it. Mixing them up is the #1 source of "column not found" and "can't use alias" errors.

Execution pipeline¶

This is how the database actually processes your query, step by step:

flowchart LR
  A["1 · FROM / JOIN"] --> B["2 · WHERE"]
  B --> C["3 · GROUP BY"]
  C --> D["4 · HAVING"]
  D --> E["5 · SELECT"]
  E --> F["6 · DISTINCT"]
  F --> G["7 · ORDER BY"]
  G --> H["8 · LIMIT / OFFSET"]

Writing vs execution¶

Click a clause to see when you write it vs when SQL runs it — and the practical gotcha that comes from the gap.

JOIN¶

JOINs let you combine rows from two tables on a related column. In our schema, the canonical example is users ⋈ orders ON users.id = orders.user_id.

SELECT *
FROM   a
INNER JOIN b ON a.id = b.a_id;

SELECT basics¶

Pick rows or columns from a table. Click each variant to see what changes.

WHERE filters¶

Filter rows by condition. Highlighted rows match the predicate.

Aggregation¶

Roll many rows up into summary numbers with GROUP BY.

CASE expressions¶

Conditional logic inline — bucket values into categories.

Subqueries and CTEs¶

Same result, three readability shapes. Find orders from US users.

Window functions¶

Calculate over partitions without collapsing rows. Output gets one extra column.

Set operations¶

Combine the rows of two queries — like Venn diagrams over result sets. Both queries must have the same number and type of columns.

Date functions (Postgres)¶

Manipulate timestamps and intervals. Reference row: created_at = '2025-03-15 14:30:00', "today" = 2026-05-10.