Tables, rows, and your first SELECT query

The actual shape of relational data — tables, rows, columns, and a primary key — and the four clauses that make up almost every SQL query you'll ever write.

Beginner

4 min read

A table is a grid, and that's the whole idea

-- The "users" table
id | name    | email               | age
---+---------+---------------------+----
1  | Ada      | ada@example.com     | 36
2  | Grace    | grace@example.com   | 41
3  | Alan     | alan@example.com    | 29

A table is a named grid: each column has a fixed name and a fixed type of data it holds (text, a number, a date), and each row is one record — one user, one order, one product. Every row in the same table has the exact same set of columns, even if some values are empty — this fixed, uniform shape is what "relational" actually refers to, and it's the whole reason SQL (Structured Query Language) can ask consistent, structured questions across every row at once, rather than each row being its own free-form blob of whatever data happened to be recorded for it.

The primary key: what makes a row uniquely identifiable

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT,
    age INTEGER
);

Every table needs a way to uniquely identify one specific row, distinct from every other row — that's the primary key, most commonly an auto-incrementing id column, guaranteed unique and never reused. Without a reliable unique identifier, "update this one specific user" or "delete this one specific order" has no unambiguous way to say which row is meant, especially once two rows could otherwise have identical data (two different users both happening to be named "Alex").

SELECT: asking for specific columns, not the whole table

SELECT name, email FROM users;

name    | email
--------+---------------------
Ada     | ada@example.com
Grace   | grace@example.com
Alan    | alan@example.com

SELECT lists exactly which columns to return; FROM says which table to pull them from. SELECT * FROM users (the * meaning "every column") is common while exploring data interactively, but naming specific columns explicitly is the better habit in real application code — it's clearer about what's actually being used, and it doesn't silently break or slow down if the table later gains new columns nobody asked for.

WHERE: filtering which rows come back

SELECT name, email FROM users WHERE age > 30;

name    | email
--------+---------------------
Ada     | ada@example.com
Grace   | grace@example.com

WHERE filters rows before they're returned — only rows where the condition evaluates to true make it into the result. Conditions can combine with AND/OR (WHERE age > 30 AND name = 'Ada'), and WHERE is evaluated per row independently, which is exactly why it can't reference an aggregate like "the average age" — at the point WHERE runs, SQL is still looking at one row at a time, with no aggregate computed yet.

ORDER BY and LIMIT: controlling result order and count

SELECT name, age FROM users ORDER BY age DESC LIMIT 2;

name    | age
--------+----
Grace   | 41
Ada     | 36

ORDER BY column DESC (or ASC, the default) sorts the results — without an explicit ORDER BY, a database makes no guarantee at all about what order rows come back in, even if they happen to come back in a consistent order during testing; relying on unspecified ordering is a real, if easy-to-miss, source of bugs that only shows up later. LIMIT caps how many rows are returned, commonly paired with ORDER BY for "give me the top N" queries — the two clauses combined are how "the 10 most recent orders" or "the 5 highest-paid employees" get expressed directly in SQL rather than fetched in full and filtered in application code.

Putting the four clauses together, in the order they're written

SELECT name, email          -- 1. which columns
FROM users                  -- 2. which table
WHERE age > 30               -- 3. filter which rows
ORDER BY name ASC            -- 4. sort the results
LIMIT 10;                    -- 5. cap the count

This is the shape underneath the overwhelming majority of everyday SQL queries — SELECT and FROM are required; WHERE, ORDER BY, and LIMIT are optional but common. Every framework's ORM (Django's QuerySets, from the Django domain's querysets-are-lazy lesson, included) ultimately compiles down to exactly this shape — Article.objects.filter(published=True).order_by("-created_at")[:10] is generating a SELECT ... WHERE ... ORDER BY ... LIMIT ... query underneath, just expressed in Python instead of SQL directly.