JOINs

A JOIN is how you rebuild the nested JSON your UI expects - without storing nested blobs.

Assume:

CREATE TABLE companies (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE contacts (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL,
  company_id INTEGER,
  FOREIGN KEY (company_id) REFERENCES companies(id)
);

INSERT INTO companies (id, name) VALUES
  (1, 'Analytical Engines'),
  (2, 'Navy Codeworks');

INSERT INTO contacts (name, email, company_id) VALUES
  ('Ada Lovelace', 'ada@example.com', 1),
  ('Grace Hopper', 'grace@example.com', 2),
  ('Lone Wolf', 'wolf@example.com', NULL);

INNER JOIN

Only rows that match on both sides:

SELECT
  c.name AS contact_name,
  co.name AS company_name
FROM contacts AS c
INNER JOIN companies AS co ON co.id = c.company_id;

Lone Wolf disappears - no company match.

FE: contacts.filter(c => c.companyId != null).map(… include company).

LEFT JOIN

Keep all rows from the left table; fill right side with NULL when missing:

SELECT
  c.name AS contact_name,
  co.name AS company_name
FROM contacts AS c
LEFT JOIN companies AS co ON co.id = c.company_id;

Now Lone Wolf shows up with company_name = NULL. This is what most "list contacts with company" screens want.

JOIN ≈ include / expand

API vibe	SQL vibe
GET /contacts?include=company	LEFT JOIN companies
only contacts that have a company	INNER JOIN
company with its contacts	join the other direction / separate query

Multiple joins

SELECT
  d.title AS deal,
  c.name AS contact,
  co.name AS company
FROM deals AS d
LEFT JOIN contacts AS c ON c.id = d.contact_id
LEFT JOIN companies AS co ON co.id = c.company_id;

Chain of includes. Watch for fan-out: joining one-to-many twice can multiply rows (one deal × many tags × many activities = surprise row count). Aggregate or query separately when that happens.

Self-join (quick mention)

A table joining itself - e.g. employees.manager_id → employees.id. Same JOIN syntax, two aliases:

SELECT e.name AS employee, m.name AS manager
FROM employees AS e
LEFT JOIN employees AS m ON m.id = e.manager_id;

Selecting the shape

You still get a flat result set from SQL. Nesting into { company: { … } } is the job of your API / ORM / serializer. SQL's job is to get the right columns onto the same row.

As you can see - JOINs aren't scary. They're just "bring the related columns along".

Next: why some of these get slow - Indexes.