Database concepts come up constantly in technical interviews and joins specifically are one of the most commonly asked - and most commonly explained poorly, with abstract definitions and no actual data to look at. This is the first part of a new series covering the database topics that show up most often in interviews: joins, indexing, stored procedures, transactions, and normalization - each as its own focused post rather than one long, exhausting piece covering everything shallowly.
The Sample Data Used Throughout This Post
CUSTOMERS table
id | name
1 | Alex
2 | Priya
3 | Sam
4 | Jordan
ORDERS table
id | customer_id | amount
101| 1 | 50.00
102| 1 | 20.00
103| 2 | 75.00
104| 5 | 30.00 -- customer_id 5 does not exist
-- in the Customers table
Notice that Jordan has no orders, and one order references a customer that doesn't exist. These two intentional gaps are what make the differences between join types actually visible in the results below - without them, every join type would return the same rows and the distinctions wouldn't be observable.
INNER JOIN: Only Matching Rows From Both Sides
INNER JOIN returns only the rows where the join condition finds a match in both tables. Anything without a match on either side is dropped entirely.
Think of a school dance where only students who found a partner get counted. Anyone standing alone, on either side of the room, doesn't appear in the final head count at all.
SELECT c.name, o.amount
FROM Customers c
INNER JOIN Orders o ON c.id = o.customer_id;
-- RESULT:
-- name | amount
-- Alex | 50.00
-- Alex | 20.00
-- Priya | 75.00
-- Jordan is gone (no matching order)
-- The order referencing customer_id 5 is gone
-- (that customer doesn't exist)
INNER JOIN is the default join for "give me records that genuinely relate to each other on both sides" - customer orders, employee assignments, product categories - any relationship where an unmatched row on either side isn't meaningful to the result.
LEFT JOIN: Everything From the Left, Matched or Not
LEFT JOIN returns every row from the left, or first-listed, table, regardless of whether a match exists on the right. Where there's no match, the right table's columns come back as NULL.
Think of a class attendance sheet listing every enrolled student, with their exam score next to their name if they took the exam, and simply blank if they didn't. Nobody gets removed from the list for not having taken the exam.
SELECT c.name, o.amount
FROM Customers c
LEFT JOIN Orders o ON c.id = o.customer_id;
-- RESULT:
-- name | amount
-- Alex | 50.00
-- Alex | 20.00
-- Priya | 75.00
-- Sam | NULL
-- Jordan | NULL
-- Sam and Jordan appear with a NULL amount - they
-- exist in Customers but have no matching order
-- The orphaned order is still gone - it has no
-- home in the LEFT table
LEFT JOIN is used for "show me all X, including ones with no related Y" - all customers including those with zero orders, all employees including those in no project yet, all products including those never sold. This is genuinely one of the most common real-world join patterns.
RIGHT JOIN: The Mirror of LEFT, Rarely Used in Practice
RIGHT JOIN is the exact mirror of LEFT JOIN - it keeps every row from the right table, with NULLs on the left where there's no match.
SELECT c.name, o.amount
FROM Customers c
RIGHT JOIN Orders o ON c.id = o.customer_id;
-- RESULT:
-- name | amount
-- Alex | 50.00
-- Alex | 20.00
-- Priya | 75.00
-- NULL | 30.00
-- The orphaned order now appears, with a NULL
-- customer name - it exists in Orders but has no
-- matching customer
-- Sam and Jordan are gone - they have no matching order
RIGHT JOIN is rarely used in practice because any RIGHT JOIN can be rewritten as a LEFT JOIN simply by swapping the order the tables are listed in. Because of this, most teams and style guides standardize on always using LEFT JOIN for consistency and readability, and RIGHT JOIN shows up mainly in code written by someone who didn't reorder the tables, or in generated SQL from a tool.
FULL OUTER JOIN: Everything From Both Sides
FULL OUTER JOIN returns every row from both tables, matched or not. Where a row on either side has no match, the other side's columns come back as NULL.
SELECT c.name, o.amount
FROM Customers c
FULL OUTER JOIN Orders o ON c.id = o.customer_id;
-- RESULT:
-- name | amount
-- Alex | 50.00
-- Alex | 20.00
-- Priya | 75.00
-- Sam | NULL
-- Jordan | NULL
-- NULL | 30.00
-- Everyone and everything shows up - matched pairs,
-- unmatched customers, AND the orphaned order
FULL OUTER JOIN is used for data integrity checks specifically - finding customers with no orders and orders with no valid customer in a single query is a genuinely common real use case, especially when auditing a dataset for referential integrity problems after a migration or a bulk import.
CROSS JOIN: Every Combination of Every Row
CROSS JOIN pairs every row in one table with every row in the other table, with no matching condition at all. A table with 10 rows crossed with a table with 10 rows produces 100 result rows.
Think of a restaurant's build-your-own-meal menu, where every size option gets paired with every topping option to enumerate all possible combinations. This is deliberate, exhaustive pairing, not a relationship between matching records.
SELECT s.size, t.topping
FROM Sizes s
CROSS JOIN Toppings t;
-- If Sizes has 3 rows (Small, Medium, Large) and
-- Toppings has 4 rows (Cheese, Pepperoni, Mushroom,
-- Olive), this returns 12 rows - every size paired
-- with every topping
CROSS JOIN is used deliberately for generating all possible combinations of two independent sets - size, color, and style combinations for a product catalog, or a calendar grid pairing every day with every hour slot. It also happens by accident - forgetting a WHERE clause or join condition entirely silently turns what should have been an INNER JOIN into a CROSS JOIN, producing a result set far larger than intended. This is a genuinely common and expensive mistake on large tables.
SELF JOIN: A Table Joined to Itself
SELF JOIN joins a table to itself, treating it as if it were two separate tables, typically to compare rows within the same table to each other.
Think of a company directory listing every employee alongside their manager's name, where the manager is also just another employee in that same directory, not a separate list of manager-type people.
-- EMPLOYEES table
-- id | name | manager_id
-- 1 | Dana | NULL
-- 2 | Alex | 1
-- 3 | Priya | 1
-- 4 | Sam | 2
SELECT e.name AS employee, m.name AS manager
FROM Employees e
LEFT JOIN Employees m ON e.manager_id = m.id;
-- RESULT:
-- employee | manager
-- Dana | NULL
-- Alex | Dana
-- Priya | Dana
-- Sam | Alex
-- The SAME table (Employees) is used twice in one
-- query, aliased as "e" and "m" to distinguish the
-- two roles it's playing
SELF JOIN is used for any hierarchical or paired relationship stored within a single table - employee and manager chains, category and subcategory trees, or finding pairs of rows that share some attribute, like two products in the same category.
Choosing the Right Join
Needing only records that genuinely match on both sides calls for INNER JOIN. Needing everything from one specific side, matched or not, calls for LEFT JOIN, reordering tables rather than reaching for RIGHT JOIN. Needing everything from both sides, to check for gaps on either end, calls for FULL OUTER JOIN. Needing every possible combination of two independent sets, on purpose, calls for CROSS JOIN. Needing to compare rows within the same table to each other calls for SELF JOIN.
Key Lessons
INNER JOIN is the default most people mean when they just say "join" - only rows with a match on both sides survive.
LEFT JOIN is genuinely one of the most common real-world join patterns - "show me all X including ones with no related Y" comes up constantly in practice.
RIGHT JOIN can always be rewritten as a LEFT JOIN by swapping table order, which is why most teams standardize on LEFT JOIN for consistency.
FULL OUTER JOIN is the right tool specifically for finding gaps on both sides of a relationship at once, such as during a data integrity audit.
CROSS JOIN is either a deliberate tool for generating combinations, or a costly accident from a missing WHERE clause - knowing which one is actually intended matters a great deal.
SELF JOIN is how hierarchical or paired data stored in a single table gets compared to itself, using the same table aliased twice in one query.
What's Next
Part 2 of this series covers indexing - clustered versus non-clustered indexes, composite indexes, and when an index actually makes a query slower instead of faster.
Summary
Every SQL join answers the same underlying question differently: which rows survive when two tables don't perfectly line up. INNER JOIN keeps only what matches. LEFT and RIGHT JOIN keep everything from one specific side. FULL OUTER JOIN keeps everything from both. CROSS JOIN ignores matching entirely and pairs everything with everything. SELF JOIN compares a table to itself. Knowing which one produces which result, not just the syntax, is what actually gets tested in an interview, and what actually matters when writing a query against real data with real gaps in it.
Originally published at TechStack Blog: https://www.techstackblog.com/post.html?slug=database-joins-explained
More from TechStack Blog:
Database: https://www.techstackblog.com/category.html?cat=database
CS Fundamentals: https://www.techstackblog.com/category.html?cat=cs-fundamentals
Top comments (0)