SQL Joins Explained

A SQL join combines rows from two or more tables based on a related column between them. Because the relational model spreads data across many small tables linked by keys, joins are how you stitch that data back together to answer real questions like "which student is enrolled in which course?"

The join type decides what happens to rows that have no match. Choosing the right type is the whole game.

The sample tables

Throughout, we use two tables:

-- student: every student
-- student_id | full_name
--     1       | Asha
--     2       | Rohan
--     3       | Meera        <- enrolled in nothing yet

-- enrolment: who is enrolled in what
-- enrolment_id | student_id | course
--      101      |     1      | DBMS
--      102      |     1      | Python
--      103      |     2      | DBMS
--      104      |     5      | Web Dev   <- student 5 does not exist in student

Notice the deliberate gaps: Meera (3) has no enrolment, and enrolment 104 points to a missing student (5). The join type determines whether those rows appear.

INNER JOIN: only matching rows

An INNER JOIN returns rows that have a match in both tables. Rows with no match on either side are dropped.

-- PostgreSQL / MySQL compatible
SELECT s.full_name, e.course
FROM student s
INNER JOIN enrolment e
    ON s.student_id = e.student_id;

-- Result:
-- Asha  | DBMS
-- Asha  | Python
-- Rohan | DBMS
-- (Meera is gone: no enrolment. Enrolment 104 is gone: no matching student.)

INNER JOIN is the most common join. Use it when you only want rows that exist on both sides.

LEFT JOIN: keep everything from the left

A LEFT JOIN (left outer join) returns all rows from the left table, plus matching rows from the right. Where there is no match, the right-side columns come back as NULL.

SELECT s.full_name, e.course
FROM student s
LEFT JOIN enrolment e
    ON s.student_id = e.student_id;

-- Result:
-- Asha  | DBMS
-- Asha  | Python
-- Rohan | DBMS
-- Meera | NULL      <- kept, because she is in the LEFT table

LEFT JOIN answers "show me every student, including those with no enrolments." It is the go-to for finding unmatched rows:

-- Students enrolled in NOTHING
SELECT s.full_name
FROM student s
LEFT JOIN enrolment e ON s.student_id = e.student_id
WHERE e.student_id IS NULL;   -- the match failed, so right side is NULL

RIGHT JOIN: keep everything from the right

A RIGHT JOIN is the mirror image — all rows from the right table, matched where possible. In practice it is rarely used because you can always rewrite it as a LEFT JOIN by swapping the table order, which most people find easier to read.

SELECT s.full_name, e.course
FROM student s
RIGHT JOIN enrolment e
    ON s.student_id = e.student_id;

-- Result includes enrolment 104 (course Web Dev) with full_name NULL,
-- because that enrolment is in the RIGHT table but has no matching student.

FULL OUTER JOIN: keep everything from both

A FULL OUTER JOIN returns all rows from both tables, matching where it can and filling NULL where it cannot.

SELECT s.full_name, e.course
FROM student s
FULL OUTER JOIN enrolment e
    ON s.student_id = e.student_id;

-- Result includes Meera (NULL course) AND Web Dev (NULL name) AND all matches.

Vendor note: PostgreSQL supports FULL OUTER JOIN directly. MySQL does not — you emulate it by UNION-ing a LEFT JOIN and a RIGHT JOIN. This is a real portability difference.

CROSS JOIN: every combination

A CROSS JOIN pairs every row of one table with every row of the other (a Cartesian product). With 3 students and 4 enrolments you get 12 rows. It is occasionally useful (e.g. generating all date-by-category combinations) but more often the accidental result of forgetting your ON condition.

SELECT s.full_name, e.course
FROM student s
CROSS JOIN enrolment e;   -- 3 x 4 = 12 rows, no condition

SELF JOIN: a table joined to itself

A SELF JOIN joins a table to itself, using aliases to tell the two copies apart. Useful for hierarchies — for example, employees and their managers in one employee table:

SELECT e.full_name AS employee, m.full_name AS manager
FROM employee e
LEFT JOIN employee m
    ON e.manager_id = m.employee_id;

Common mistakes

• Forgetting the ON condition. A join without a join condition becomes a CROSS JOIN, exploding your row count. Always specify how the tables relate.
• Using INNER JOIN when you needed LEFT JOIN. If rows silently disappear (e.g. students with no orders vanish from a report), you probably wanted to keep the unmatched rows.
• Filtering an outer join in WHERE instead of ON. Putting a condition on the right table in WHERE turns a LEFT JOIN back into an effective INNER JOIN, because NULL rows fail the condition. Put such conditions in the ON clause.
• Ambiguous column names. When both tables have a student_id, you must qualify it (s.student_id). Always alias your tables.
• Assuming MySQL supports FULL OUTER JOIN. It does not — emulate with UNION.

FAQ

What is the difference between WHERE and ON in a join? ON defines how rows match during the join; WHERE filters the result afterwards. For inner joins the distinction rarely matters, but for outer joins it changes which unmatched rows survive.

Can I join more than two tables? Yes — chain JOIN clauses. Each join links one more table to the growing result. Three- and four-table joins are routine.

Which join is fastest? There is no universal answer; it depends on indexes and data sizes. Proper indexes on the join columns matter far more than the join type.

Keep learning

Joins build on keys in DBMS and feed naturally into GROUP BY & aggregate functions and subqueries.

Want to practise joins on realistic multi-table datasets? Join the waitlist for the DBMS & SQL course at Infoplanet, Jalgaon.