In Class 20 you split data into separate tables on purpose: companies live in companies, reports live in research_reports, and a foreign key — the company_id stored on each report — points from the report to its company. That design keeps each fact in one reliable home. But products rarely show one table at a time. A research page shows a report *alongside* its company name and sector. Some operation has to reunite the tables at question time.

That operation is the JOIN.

Foreign keys make joins possible; joins make them useful

Recall the two tables:

companies                          research_reports
id | name       | sector           id  | company_id | report_date | rating
---+------------+---------         ----+------------+-------------+-------
 1 | Acme Corp  | Energy            101 |    1       | 2026-08-14  | Buy
 2 | Beta Inc   | Healthcare        102 |    1       | 2026-05-30  | Hold
 3 | Gamma LLC  | Energy            103 |    2       | 2026-07-02  | Buy
                                    104 |    99      | 2026-06-15  | Hold

research_reports.company_id is a foreign key pointing at companies.id. A JOIN uses that pointer at query time to stitch matching rows side by side. Note the two edge rows built into this printed set: Gamma LLC (id 3) has no report, and report 104 points at company_id 99, which does not exist.

INNER JOIN: match every report with its company

The everyday join answers: "show each report together with its company details."

SELECT r.report_date, r.rating, c.name, c.sector
FROM research_reports AS r
INNER JOIN companies AS c
  ON r.company_id = c.id
ORDER BY r.report_date DESC
LIMIT 20;

In English: "For every report with a matching company, return report fields plus company name and sector." AS r and AS c are short aliases. The ON clause states the matching rule — almost always foreign key equals primary key.

The defining property of an inner join: rows without a match on both sides disappear. A report whose company_id points nowhere is dropped; a company with no reports never appears. Use an inner join when the question is about the *linked* records: "all published reports with company context."

LEFT JOIN: all companies, even those without a report

Now flip the question: "show all companies, even those without a report yet." That is a LEFT JOIN — keep everything on the left (first) table, attach matches where they exist, fill in NULLs where they do not.

SELECT c.name, c.sector, r.report_date, r.rating
FROM companies AS c
LEFT JOIN research_reports AS r
  ON r.company_id = c.id
ORDER BY c.name;

Against the sample data, Gamma LLC has no report. The inner-join version omits Gamma; the left-join version returns it with NULL in the report columns — "no match found," and exactly the "companies needing coverage" list a Sonariq editor would want.

Memory aid: the join keeps all rows from the table named first (FROM companies), plus matches from the joined table. Swap the table order and you change which side is preserved — so read FROM … LEFT JOIN … as a sentence: "starting from all companies, bring in matching reports where they exist."

Why joins go wrong: missing keys, wrong keys, duplicates, empties

Almost every broken join traces to the linking IDs, not the JOIN keyword:

1. Empty result. The ON condition matches nothing — usually a wrong column (ON r.id = c.id instead of ON r.company_id = c.id) or a filter that eliminates everything. The query runs fine and returns zero rows, which feels like missing data rather than a bug.

2. Missing rows. An inner join drops unmatched rows by design. If companies vanish from a report that should list all of them, the query probably needs a LEFT JOIN, not an inner one. Ask: "should unmatched rows appear with blanks, or disappear?" That answer picks the join type.

3. Duplicated rows. If the joined table holds several rows per key — two company rows sharing id = 1 — every report for company 1 appears twice. Sudden row-count inflation after adding a join is the signature.

4. Wrong matches. Joining on a name instead of the ID links every similarly named row, including stale renames. Always join on the key, never on retypeable text.

The fix in every case is the same debugging sequence below.

Debug joins in this order

When a join surprises you, work small:

1. Inspect the IDs. SELECT id FROM companies LIMIT 5; and SELECT DISTINCT company_id FROM research_reports LIMIT 5; Do the sets overlap? 2. Query each table alone with its filter and a LIMIT before combining. 3. Join with a small LIMIT. Check the count first: exploded (duplicates) or zero (no matches)? 4. Spot-check one ID on both sides explicitly (WHERE id = 1, WHERE company_id = 1).

Only two join types belong in your toolkit now — inner and left. A beginner who can explain and debug these two can answer nearly every early product question. Breadth can wait; correctness cannot.

Visual: highlighted IDs become one combined row

companies                    research_reports              INNER JOIN result
id | name                    company_id | report_date      name      | report_date
---+---------                -----------+-----------       ----------+-----------
[1]| Acme Corp        ──────► [1]       | 2026-08-14  ═══► Acme Corp | 2026-08-14
 2 | Beta Inc         ──────►  1        | 2026-05-30  ═══► Acme Corp | 2026-05-30
 3 | Gamma LLC (no report)    2         | 2026-07-02  ═══► Beta Inc  | 2026-07-02

LEFT JOIN result keeps Gamma LLC with NULLs:
 Acme Corp | 2026-08-14 |  Gamma LLC | NULL (no matching report)

Trace one ID from the left table to its partner on the right, then across to the combined row. Any combined row you cannot trace this way reveals the bug's location.

Worked prediction on the printed tables: inner vs left

Do this on the page before inventing your own tables. Use the printed companies (ids 1–3) and research_reports (ids 101–104) above.

INNER JOIN — reports with matching companies only.

SELECT c.name, r.id AS report_id, r.report_date, r.rating
FROM research_reports AS r
INNER JOIN companies AS c
  ON r.company_id = c.id
ORDER BY r.report_date DESC;

Expected result (3 rows — report 104 dropped, Gamma LLC absent):

name      | report_id | report_date | rating | ID pair
----------+-----------+-------------+--------+--------
Acme Corp | 101       | 2026-08-14  | Buy    | r.company_id 1 = c.id 1
Beta Inc  | 103       | 2026-07-02  | Buy    | r.company_id 2 = c.id 2
Acme Corp | 102       | 2026-05-30  | Hold   | r.company_id 1 = c.id 1

Report 104 (company_id 99) vanishes because 99 matches no companies.id — inner join drops unmatched rows by design.

LEFT JOIN from companies — all companies, reports where they exist.

SELECT c.name, r.id AS report_id, r.report_date, r.rating
FROM companies AS c
LEFT JOIN research_reports AS r
  ON r.company_id = c.id
ORDER BY c.name, r.report_date DESC;

Expected result (4 rows — Gamma LLC kept with NULLs):

name      | report_id | report_date | rating | ID pair
----------+-----------+-------------+--------+--------
Acme Corp | 101       | 2026-08-14  | Buy    | 1 = 1
Acme Corp | 102       | 2026-05-30  | Hold   | 1 = 1
Beta Inc  | 103       | 2026-07-02  | Buy    | 2 = 2
Gamma LLC | NULL      | NULL        | NULL   | id 3 has no match — NULLs

Report 104 never appears here either: a left join from companies keeps all *companies*, not all reports. Predict both tables by ID only — cover the name column while tracing — then compare. Every result row must point to one exact ID pair; a row you cannot trace means the mental model needs the fix.

Practical exercise: predict on paper before AI writes

1. First, reproduce the two worked predictions above on paper from the printed tables: write out the inner-join rows and the left-join rows by hand, marking every NULL and every dropped row (report 104, Gamma LLC). 2. Optional stretch: draw your own tiny variant (change one ID or add one row), predict both joins again by hand, then compare. 3. Now ask an AI assistant to write both queries for your tables, run them (in a SQL editor, a sample database, or a scratch SQLite file), and compare the actual results against your predictions. Investigate every difference with the four-step debug sequence.

Finish line: paper tables plus predicted and actual results for both join types, with any mismatch explained in one sentence ("report 104 vanished because its company_id has no match — inner join drops it").

Verify: for each result row, point to the exact ID pair that produced it. If a row has no traceable ID pair, your mental model — not the database — needs the fix.

Common failure mode: predicting from company names instead of IDs, then being surprised when the database matches IDs. Cover the name column while predicting; use only the numbers.

Check your understanding

1. In plain language, what does an inner join keep — and what does it drop? 2. When would you choose a LEFT JOIN over an inner join? 3. A joined query returns twice as many rows as the left table has. What is the most likely cause? 4. What are the four steps of the join debugging sequence?