A SQL join combines rows from related tables; the right join depends on which unmatched records you need to keep. An INNER JOIN keeps matching pairs, while a LEFT JOIN keeps every row from its left-hand table and fills right-hand columns with NULL when there is no match. The six queries below use a fictional beekeeping co-op to show how that choice changes the result.
How to read the example
Suppose the co-op tracks members and the apiaries they manage. A member can have no apiary or one apiary in this small example; an apiary may be assigned to a member or be awaiting assignment.
Each table has an identifier that uniquely names one record: members.member_id identifies a member, and apiaries.apiary_id identifies an apiary. The apiaries.member_id column refers to a member’s identifier. Such a reference is a foreign key; the column it points to is a key. The join condition connects those related columns.
| members | apiaries | ||
|---|---|---|---|
| member_id | member_name | apiary_id | member_id |
| 1 | Ada | 101 | 1 |
| 2 | Ben | 102 | 3 |
| 3 | Cy | 103 | NULL |
| 4 | Dee |
For clarity, the apiaries also have names: 101 is Clover Hill, 102 is North Field, and 103 is Meadow Lot. In this data, Ada’s apiary is assigned to member 1, North Field is assigned to member 3, Meadow Lot has no assigned member, and Ben and Dee have no apiary. The examples use explicit JOIN ... ON syntax, which makes the matching rule visible separately from any later filtering. Qualifying columns with table names or aliases also helps prevent ambiguous references. See the PostgreSQL tutorial on joins between tables and Microsoft Learn’s SQL Server joins documentation.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →#1 Best Overall
1. INNER JOIN: show only members with an apiary
SELECT m.member_name, a.apiary_name
FROM members AS m
INNER JOIN apiaries AS a
ON a.member_id = m.member_id;The condition pairs records whose member identifiers match. This returns Ada with Clover Hill and Cy with North Field: two rows. Ben and Dee are omitted because they have no matching apiary, and Meadow Lot is omitted because no member is assigned to it.
2. LEFT JOIN: keep every member
SELECT m.member_name, a.apiary_name
FROM members AS m
LEFT JOIN apiaries AS a
ON a.member_id = m.member_id;A LEFT JOIN preserves every row from its left input, here members. The result has four rows: Ada and Cy have matching apiary names, while Ben and Dee appear with NULL for apiary_name. The unassigned Meadow Lot still does not appear because it is on the right side.
3. RIGHT JOIN: keep every apiary
SELECT m.member_name, a.apiary_name
FROM members AS m
RIGHT JOIN apiaries AS a
ON a.member_id = m.member_id;A RIGHT JOIN preserves every row from the right input, apiaries. It returns three rows: Clover Hill with Ada, North Field with Cy, and Meadow Lot with NULL for the member name. Ben and Dee do not appear because they are unmatched rows on the left.
You can express the same preservation with a left join by reversing the table order:
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →SELECT m.member_name, a.apiary_name
FROM apiaries AS a
LEFT JOIN members AS m
ON m.member_id = a.member_id;4. FULL JOIN: keep unmatched rows from both tables
SELECT m.member_name, a.apiary_name
FROM members AS m
FULL JOIN apiaries AS a
ON a.member_id = m.member_id;A FULL JOIN preserves matching pairs plus unmatched rows from both inputs. This result has five rows: two matched pairs, Ben and Dee with no apiary, and Meadow Lot with no member. Where one side has no match, its columns are NULL.
5. CROSS JOIN: deliberately pair every member with every apiary
SELECT m.member_name, a.apiary_name
FROM members AS m
CROSS JOIN apiaries AS a;A CROSS JOIN has no matching condition: it produces every possible combination. With four members and three apiaries, this returns 12 rows (4 × 3). That can be useful when every combination is intended, such as building a complete member-by-apiary planning grid; it is usually not the right substitute for a relationship-based join.
6. Self-join: compare members in the same table
A join can relate a table to itself. For example, pair each member with members whose identifier is higher, so each two-member combination appears once:
SELECT m1.member_name AS first_member,
m2.member_name AS second_member
FROM members AS m1
INNER JOIN members AS m2
ON m1.member_id < m2.member_id;The aliases m1 and m2 let the query refer to the two roles separately. With four members, the condition returns six pairs: Ada–Ben, Ada–Cy, Ada–Dee, Ben–Cy, Ben–Dee, and Cy–Dee. The condition is deliberately different from the equality condition used to connect members to apiaries.
Choose a join by the rows you must retain
| Join | Rows preserved | Result in this example |
|---|---|---|
INNER JOIN |
Only matching pairs | 2 member–apiary pairs |
LEFT JOIN |
Every left-side row, matched or not | 4 member rows |
RIGHT JOIN |
Every right-side row, matched or not | 3 apiary rows |
FULL JOIN |
Every row from both sides, matched where possible | 5 total rows |
CROSS JOIN |
Every possible row combination | 12 combinations |
These are the documented join behaviors in PostgreSQL 18’s table expressions documentation; consult the documentation for your database when checking dialect-specific syntax and behavior.
Keep join conditions and filters distinct
ON states how rows match. You can also write USING (member_id) when the intended join columns share a name, as they do here; it is a concise alternative for this simple relationship. Avoid using NATURAL JOIN casually: it infers matches from every same-named column, so adding a same-named column later can silently change the join condition. PostgreSQL documents both forms and that schema sensitivity in its table expressions reference.
Be especially deliberate about filters after an outer join. For example, to keep every member but include only apiary matches whose name is Clover Hill, put the restriction in ON:
SELECT m.member_name, a.apiary_name
FROM members AS m
LEFT JOIN apiaries AS a
ON a.member_id = m.member_id
AND a.apiary_name = 'Clover Hill';This still returns all four members; only Ada has a matching apiary name, and the other right-side values are NULL. If instead you put a.apiary_name = 'Clover Hill' in WHERE, rows with NULL apiary names fail that condition, so the result no longer keeps members without that match. Put a restriction in WHERE when you mean to filter the completed result; put it in ON when it should limit matches without discarding left-side rows.
Recommended Free Tools
What a join means for execution
It is useful to imagine a join checking candidate row pairs against its condition, but that is a conceptual model, not a claim about how the database physically executes every query. PostgreSQL notes that actual execution is usually more efficient than a literal pair-by-pair process. SQL Server documentation describes the optimizer choosing physical join algorithms and join order based on factors such as table size, indexes, and data distribution. Therefore, choose join syntax for the result you need rather than assuming one written form guarantees a particular execution strategy. See the PostgreSQL joins tutorial and SQL Server joins reference.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
