SQL Joins Explained: A Beekeeping Co-op in Six Queries

A join is not a special SQL feature reserved for experts. It is the answer to a problem every relational database creates on purpose: your data lives in more than one table, and a single query rarely cares about table boundaries.

To see how each join behaves, forget sales orders and customer records for a moment. We'll run a small urban beekeeping co-op instead: a handful of keepers, their hives, and the honey those hives produce. The data is small enough to hold in your head, which makes it easier to see exactly what each join adds or drops.

The Setup: Three Tables That Don't Line Up Perfectly

Here's the schema.

CREATE TABLE beekeepers (
    keeper_id INT PRIMARY KEY,
    name      VARCHAR(50),
    mentor_id INT REFERENCES beekeepers(keeper_id)
);

CREATE TABLE hives (
    hive_id           INT PRIMARY KEY,
    keeper_id         INT REFERENCES beekeepers(keeper_id),
    location          VARCHAR(50),
    established_year  INT
);

CREATE TABLE harvests (
    harvest_id    VARCHAR(5) PRIMARY KEY,
    hive_id       INT,
    harvest_date  DATE,
    honey_kg      DECIMAL(5,1)
);

And the data, deliberately messy in a realistic way:

beekeepers

keeper_id	name	mentor_id
1	Amara Wanjiru	NULL
2	Brian Otieno	1
3	Chiara Mwangi	1
4	David Kimani	NULL

hives

hive_id	keeper_id	location	established_year
101	1	Ruiru Rooftop	2021
102	2	Thika Road Garden	2022
103	3	Kiambu Backyard	2023
104	99	Abandoned Lot	2020

harvests

harvest_id	hive_id	harvest_date	honey_kg
H1	101	2024-05-01	12.5
H2	101	2024-09-01	9.0
H3	102	2024-06-15	15.0
H4	999	2024-07-01	5.0

Notice three deliberate cracks in this data. David Kimani (keeper 4) has never registered a hive. Hive 104 lists keeper_id = 99, a keeper who doesn't exist in the beekeepers table at all, maybe a data-entry error, maybe a keeper who left the co-op and got deleted. And harvest H4 points to hive_id = 999, a hive that was never created, likely a typo when someone logged the harvest by hand. Real databases have rows like this. A good demonstration of joins should too.

What a Join Actually Does

A join combines rows from two tables based on a matching condition, usually a shared key. The database doesn't merge the tables permanently; it builds a temporary result set for that one query, row by row, checking the condition each time.

The six join types differ only in one decision: what happens to a row that has no match on the other side. Some joins throw it away. Others keep it and fill the missing columns with NULL. Once you see that single rule at work, every join becomes predictable instead of memorized.

The Joins, One at a Time

1. INNER JOIN: only the rows that match on both sides

SELECT b.name, h.location, h.established_year
FROM beekeepers b
INNER JOIN hives h ON b.keeper_id = h.keeper_id;

Result:

name	location	established_year
Amara Wanjiru	Ruiru Rooftop	2021
Brian Otieno	Thika Road Garden	2022
Chiara Mwangi	Kiambu Backyard	2023

David Kimani disappears because he has no hive. Hive 104 disappears because its keeper doesn't exist. INNER JOIN is strict: a row survives only if both sides agree it exists.

Use it when an unmatched row would be meaningless in context, for example, a report on "active hive locations by keeper" has no use for a keeper with zero hives.

2. LEFT JOIN: keep everyone from the first table, matched or not

SELECT b.name, h.location, h.established_year
FROM beekeepers b
LEFT JOIN hives h ON b.keeper_id = h.keeper_id;

Result:

name	location	established_year
Amara Wanjiru	Ruiru Rooftop	2021
Brian Otieno	Thika Road Garden	2022
Chiara Mwangi	Kiambu Backyard	2023
David Kimani	NULL	NULL

David is back, with NULL standing in for the hive data he doesn't have. LEFT JOIN never drops a row from the table named on the left of the keyword, no matter what's missing on the right.

Use it whenever the absence itself is the answer you're looking for, a membership list that should show every member even if some haven't done anything yet.

3. RIGHT JOIN: keep everyone from the second table, matched or not

SELECT b.name, h.location, h.established_year
FROM beekeepers b
RIGHT JOIN hives h ON b.keeper_id = h.keeper_id;

Result:

name	location	established_year
Amara Wanjiru	Ruiru Rooftop	2021
Brian Otieno	Thika Road Garden	2022
Chiara Mwangi	Kiambu Backyard	2023
NULL	Abandoned Lot	2020

The mirror image of the previous query. Every hive shows up, including hive 104, whose orphaned keeper_id = 99 produces a NULL name. RIGHT JOIN is rare in practice mostly because you can rewrite it as a LEFT JOIN by swapping the table order, and most style guides prefer that for readability. It's worth knowing, though, especially when you're editing someone else's query rather than writing your own from scratch.

4. FULL OUTER JOIN: keep every row from both sides

SELECT b.name, h.location, h.established_year
FROM beekeepers b
FULL OUTER JOIN hives h ON b.keeper_id = h.keeper_id;

Result:

name	location	established_year
Amara Wanjiru	Ruiru Rooftop	2021
Brian Otieno	Thika Road Garden	2022
Chiara Mwangi	Kiambu Backyard	2023
David Kimani	NULL	NULL
NULL	Abandoned Lot	2020

This is the union of the LEFT and RIGHT results: nothing gets dropped from either table. It's the join to reach for during data audits, where the whole point is finding orphaned or incomplete records on both sides at once. One caution: MySQL doesn't support FULL OUTER JOIN directly. You emulate it with LEFT JOIN ... UNION ... RIGHT JOIN. PostgreSQL, SQL Server, and Oracle all support the keyword as written above.

5. CROSS JOIN: every row paired with every row, no condition at all

Say the co-op is scheduling two workshops and wants a sign-up grid with one row per keeper, per workshop.

CREATE TABLE workshops (
    workshop_id VARCHAR(3),
    topic       VARCHAR(50),
    workshop_date DATE
);
-- W1, Swarm Prevention, 2024-03-10
-- W2, Honey Extraction, 2024-03-17

SELECT b.name, w.topic, w.workshop_date
FROM beekeepers b
CROSS JOIN workshops w;

Result:

name	topic	workshop_date
Amara Wanjiru	Swarm Prevention	2024-03-10
Amara Wanjiru	Honey Extraction	2024-03-17
Brian Otieno	Swarm Prevention	2024-03-10
Brian Otieno	Honey Extraction	2024-03-17
Chiara Mwangi	Swarm Prevention	2024-03-10
Chiara Mwangi	Honey Extraction	2024-03-17
David Kimani	Swarm Prevention	2024-03-10
David Kimani	Honey Extraction	2024-03-17

Four keepers times two workshops gives eight rows. No ON clause, no matching logic, just every possible combination. CROSS JOIN is the one to be careful with: on two tables of ten thousand rows each, you'd get a hundred million rows. Reach for it deliberately, for generating combinations, filling calendar grids, building test data, not by forgetting a WHERE clause on a regular join.

6. SELF JOIN: a table matched against itself

The beekeepers table already tracks mentorship through mentor_id. To turn that into a readable list of who trained whom, join the table to a second copy of itself.

SELECT apprentice.name AS apprentice, mentor.name AS mentor
FROM beekeepers apprentice
INNER JOIN beekeepers mentor ON apprentice.mentor_id = mentor.keeper_id;

Result:

apprentice	mentor
Brian Otieno	Amara Wanjiru
Chiara Mwangi	Amara Wanjiru

There's only one physical table here. The two aliases, apprentice and mentor, let the database treat it as two separate tables for the length of the query. Amara and David don't appear as apprentices because their mentor_id is NULL, an INNER JOIN drops them the same way it would drop any other unmatched row. Swap in a LEFT JOIN if you want every keeper listed, with NULL in the mentor column for those who trained themselves.

A self join is the right tool whenever a table describes a relationship between rows in its own set: employees and managers, cities and neighboring cities, comments and their replies.

Bringing In the Harvests

So far every example has used beekeepers and hives. The harvests table has been sitting there unused, which is its own kind of realistic: plenty of production databases have a table nobody's queried in months. Let's put it to work and see how a hive with no harvest, and a harvest with no hive, behave.

SELECT h.hive_id, h.location, hv.harvest_date, hv.honey_kg
FROM hives h
LEFT JOIN harvests hv ON h.hive_id = hv.hive_id;

Result:

hive_id	location	harvest_date	honey_kg
101	Ruiru Rooftop	2024-05-01	12.5
101	Ruiru Rooftop	2024-09-01	9.0
102	Thika Road Garden	2024-06-15	15.0
103	Kiambu Backyard	NULL	NULL
104	Abandoned Lot	NULL	NULL

Hive 101 shows up twice because it has two harvest records, a reminder that a join's row count follows the data, not the table it started from. Hive 103, Chiara's hive, is only a year old and hasn't been harvested yet, so it appears once with NULL in place of harvest data. LEFT JOIN keeps it in the results instead of hiding a hive that simply hasn't produced anything yet.

Now flip it around and check for harvest H4's orphaned hive_id:

SELECT hv.harvest_id, hv.hive_id, hv.honey_kg, h.location
FROM harvests hv
LEFT JOIN hives h ON hv.hive_id = h.hive_id;

Result:

harvest_id	hive_id	honey_kg	location
H1	101	12.5	Ruiru Rooftop
H2	101	9.0	Ruiru Rooftop
H3	102	15.0	Thika Road Garden
H4	999	5.0	NULL

There it is: 5.0 kg of honey logged against a hive that doesn't exist in the hives table. In a real co-op this is exactly the kind of row a data-quality check should flag, and a LEFT JOIN followed by WHERE h.hive_id IS NULL is the standard way to find it.

Chain all three tables together and you get the report the co-op actually wants: total honey per keeper, including keepers who haven't harvested a drop.

SELECT b.name, COALESCE(SUM(hv.honey_kg), 0) AS total_honey_kg
FROM beekeepers b
LEFT JOIN hives h ON b.keeper_id = h.keeper_id
LEFT JOIN harvests hv ON h.hive_id = hv.hive_id
GROUP BY b.name;

Result:

name	total_honey_kg
Amara Wanjiru	21.5
Brian Otieno	15.0
Chiara Mwangi	0.0
David Kimani	0.0

Two LEFT JOINs in a row, one linking keepers to hives, the next linking hives to harvests, and every keeper still makes the list even if the chain runs into a NULL partway through. This is the pattern behind most real reporting queries: keep the entity you're reporting on, then reach outward through as many related tables as the question needs.

Choosing the Right One

You want...	Use
Only rows with a confirmed match on both sides	INNER JOIN
Every row from the main table, matches or not	LEFT JOIN
Every row from the secondary table, matches or not	RIGHT JOIN
Every row from both tables, to catch mismatches on either side	FULL OUTER JOIN
Every possible combination, with no matching condition	CROSS JOIN
Rows from a table related to other rows in the same table	SELF JOIN

Try It Yourself

The fastest way to make this stick is to break your own data the way hive 104 is broken here: create two small tables, delete a foreign key's parent row on purpose, then run all six joins against them and watch what changes. The gaps are where the learning happens.