Lesson Beginner

How to read tables/ERDs

Reading tables and ERDs is the fastest way to understand an unfamiliar dataset.
Before writing complex SQL, you should know table grain, keys, and relationships.

Why This Comes Before Complex SQL #

Many SQL mistakes are not syntax mistakes.
They happen because the table model is misunderstood: wrong grain, wrong key, or connecting tables the wrong way.
If you read the model first, your query logic gets much more reliable.

A Fast ERD Reading Sequence #

When you open a new schema, inspect in this order:

Grain: one row represents what?
Primary key: what uniquely identifies a row?
Foreign keys: how tables connect
Cardinality: one-to-one, one-to-many, or many-to-many
Critical columns: dates, statuses, measures, and nullable fields

First-Look Queries #

A diagram tells you what the data should look like. Peeking at a few real rows confirms it.
The simplest first-look pattern is one tiny query per table:

sql

-- Peek at a few real rows from each table
SELECT * FROM users LIMIT 5;

SELECT * FROM orders LIMIT 5;

That is all the SQL this lesson needs. SELECT * FROM table LIMIT 5 means "show me every column for the first five rows". The next lesson covers SELECT and FROM properly.

Worked Example: Users and Orders #

The users and orders tables already exist in this IDE. Here is the shape of each table:

sql

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email VARCHAR,
  name VARCHAR,
  created_at DATE
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  user_id INTEGER,
  product_id INTEGER,
  quantity INTEGER,
  order_date DATE,
  status VARCHAR,
  created_at TIMESTAMP,
  total DECIMAL(10,2)
);

Read the model:

users.id uniquely identifies a user (primary key).
orders.user_id points back to users.id (foreign key).
One user can have many orders. One order belongs to one user. That is a one-to-many relationship.

What does that foreign key buy you? It lets the database combine the two tables into one result. For example, you can show each order next to the name of the user who placed it:

sql

-- Preview: combining two tables through a foreign key
SELECT
  users.name,
  orders.id AS order_id,
  orders.total
FROM users
JOIN orders
  ON orders.user_id = users.id
LIMIT 5;

This is a join. You will learn to write these yourself in the Joins section, so do not worry about the syntax yet.
For now, just notice the line ON orders.user_id = users.id: that is the foreign key from the ERD doing its job, matching each order to its user.
Because the relationship is one-to-many, the same user name can appear on several rows, one row per order.

Try It: Trace a Relationship by Hand #

Run the query below, then swap users for orders and rerun.
Look at the user_id column in orders and find the matching id in users. That is you reading the relationship directly from the data.

Loading SQL editor...

Habits to Keep #

Always ask "one row = what?" before reading anything else in a table.
Find the primary key, then trace each foreign key to the table it points to.
Name the cardinality of every relationship: one-to-one, one-to-many, or many-to-many.
Peek at a few real rows with SELECT * FROM table LIMIT 5 to confirm the diagram matches the data.

Next Step #

Continue to SELECT / FROM and apply ERD-reading checks before each new query.