SQL Joins Explained with Examples (INNER, LEFT, RIGHT, FULL)

Real data is spread across tables: students in one, enrollments in another, payments in a third. A JOIN combines rows from two tables using a related column. Joins are asked in almost every data analyst, data engineer and backend interview.

Sample tables

students

enrollments

Notice: Sameer (id 3) has no enrollment, and the Tableau row points to student 4, who does not exist.

INNER JOIN: only matching rows

SELECT s.name, e.course
FROM students s
INNER JOIN enrollments e ON e.student_id = s.id;

Sameer and the orphan Tableau row disappear, because they have no match. JOIN on its own means INNER JOIN.

LEFT JOIN: all rows from the left table

SELECT s.name, e.course
FROM students s
LEFT JOIN enrollments e ON e.student_id = s.id;

Every student appears. Where there is no match, the right-side columns are NULL.

Find rows with no match

A very common business question: *which students have not enrolled in anything?*

SELECT s.name
FROM students s
LEFT JOIN enrollments e ON e.student_id = s.id
WHERE e.student_id IS NULL;

Result: Sameer.

RIGHT JOIN: all rows from the right table

SELECT s.name, e.course
FROM students s
RIGHT JOIN enrollments e ON e.student_id = s.id;

Most people swap the table order and write a LEFT JOIN instead, which is easier to read.

FULL OUTER JOIN: everything from both sides

SELECT s.name, e.course
FROM students s
FULL OUTER JOIN enrollments e ON e.student_id = s.id;

Returns all matches plus Sameer (with NULL course) and Tableau (with NULL name). Useful for reconciliation, for example comparing two systems' records. MySQL does not support FULL OUTER JOIN directly; combine a LEFT and a RIGHT join with UNION.

SELF JOIN: a table joined to itself

employees: id, name, manager_id

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

Join plus aggregation

*How many courses has each student taken, including zero?*

SELECT s.name, COUNT(e.course) AS courses
FROM students s
LEFT JOIN enrollments e ON e.student_id = s.id
GROUP BY s.name
ORDER BY courses DESC;

COUNT(e.course) counts only non-NULL values, so Sameer correctly shows 0. COUNT(*) would wrongly show 1.

Common mistakes

• Filtering the right table in WHERE after a LEFT JOIN: WHERE e.course = 'SQL' removes the NULL rows and silently turns it into an inner join. Put that condition in the ON clause instead if you want to keep all left rows.
• Duplicate rows: joining on a non-unique key multiplies rows. Always check row counts before and after a join.
• Missing join condition: produces a Cartesian product (every row times every row).

Quick summary

Interview questions

• Difference between WHERE and ON in a LEFT JOIN? ON decides which rows match; WHERE filters the final result, which can remove unmatched rows.
• How do you find duplicates? GROUP BY the columns and keep groups with HAVING COUNT(*) > 1.
• UNION vs UNION ALL? UNION removes duplicates (slower); UNION ALL keeps every row.

Next steps

Practise on a free dataset with PostgreSQL or SQLite. Then learn data cleaning with pandas, or join the Data Analytics + AI course for SQL, Power BI and real dashboards.