SQL Joins Explained
Inner, left, right, full and cross joins with one small dataset and the exact output of each. Also the NOT IN trap and the ON versus WHERE mistake that catches people in interviews.
A SQL join combines rows from two tables on a condition. INNER JOIN keeps only rows matching on both sides, LEFT JOIN keeps every left row and fills missing matches with NULL, and CROSS JOIN pairs every row with every row. Interviewers expect the trap: a WHERE condition on the right table turns a left join into an inner join.
A SQL join combines rows from two tables based on a condition. INNER JOIN keeps only matching rows, LEFT JOIN keeps every row from the left table and fills missing matches with NULL, and CROSS JOIN pairs every row with every row.
- An INNER JOIN keeps only rows that match on both sides, so rows with a NULL or non-existent foreign key disappear from the result.
- A LEFT JOIN keeps every row from the left table and returns NULL in the right table's columns where nothing matched.
- A CROSS JOIN has no condition and returns rows(A) times rows(B) rows, for example 4 x 3 = 12.
- Putting a condition on the right table in WHERE instead of ON filters out the NULL rows and makes a LEFT JOIN behave like an INNER JOIN.
- In SQL, NOT IN returns no rows when its subquery contains a NULL, because comparing with NULL is unknown, so NOT EXISTS or an anti-join is safer.
- FULL OUTER JOIN keeps unmatched rows from both sides; SQLite added it in version 3.39, and MySQL does not support it.
01The two tables used below
All the outputs in this article were produced by running the queries on SQLite 3.45. Two tables:
create table student(id int, name text, dept_id int);
create table dept(id int, name text);
insert into student values
(1,'Asha',10), (2,'Ravi',20), (3,'Meena',NULL), (4,'Kiran',30);
insert into dept values
(10,'CSE'), (20,'ECE'), (40,'MECH');Three things to notice. Meena has no department (NULL). Kiran points at department 30, which does not exist. And MECH (40) has no students. Those three cases are exactly what separate the join types.
02INNER JOIN
select s.name, d.name
from student s
join dept d on s.dept_id = d.id;Result: (Asha, CSE) and (Ravi, ECE). Meena is gone because NULL never equals anything, Kiran is gone because there is no department 30, and MECH is gone because no student points at it.
JOIN on its own means INNER JOIN. Use it when a row without a match is of no interest to you.
03LEFT JOIN and RIGHT JOIN
select s.name, d.name
from student s
left join dept d on s.dept_id = d.id;Result: Asha CSE, Ravi ECE, Meena NULL, Kiran NULL. All four students are present. Where no department matched, the department column is NULL.
RIGHT JOIN is the mirror image. Written from the other side, from dept d left join student s ... returns CSE with Asha, ECE with Ravi, and MECH with NULL for the student. Most people write left joins only and swap the table order, which keeps queries easier to read.
FULL OUTER JOIN keeps unmatched rows from both sides. SQLite only added it in version 3.39, and MySQL does not support it at all, where people combine a left and right join with UNION.
A very useful pattern is the anti-join: find rows with no match. Left join, then keep rows where the right side is NULL.
select s.name
from student s
left join dept d on s.dept_id = d.id
where d.id is null; -- Meena, Kiran04CROSS JOIN
select count(*) from student cross join dept; -- 12A cross join has no condition. The result has rows(A) times rows(B) rows, so it grows fast. It has real uses, such as generating every combination of size and colour for a product catalogue.
The usual way people get one by accident is forgetting the join condition. If a query that should return 5,000 rows suddenly returns 25 million, check that every join has its ON.
05ON versus WHERE in an outer join
Put the same condition in two places and the answers differ.
-- condition in WHERE
select s.name, d.name from student s
left join dept d on s.dept_id = d.id
where d.name = 'CSE';
-- Asha, CSE (one row)
-- condition in ON
select s.name, d.name from student s
left join dept d on s.dept_id = d.id and d.name = 'CSE';
-- Asha CSE, Ravi NULL, Meena NULL, Kiran NULL (four rows)With WHERE, the filter runs after the join. Rows where the department is NULL fail d.name = 'CSE', so they disappear, and the left join behaves like an inner join. With the condition in ON, it only decides which rows match, and unmatched students are still kept.
Rule of thumb: conditions about the right table go in ON if you want to keep unmatched left rows.
06NULL traps: NOT IN and COUNT
Which departments have no students? The obvious query is wrong.
select count(*) from dept
where id not in (select dept_id from student); -- 0The subquery returns 10, 20, NULL, 30. For MECH (40), the database has to evaluate 40 <> NULL, which is unknown, not true, so the row is rejected. One NULL in the list makes NOT IN return nothing at all. Use NOT EXISTS or an anti-join instead.
select name from dept dd
where not exists (select 1 from student s where s.dept_id = dd.id); -- MECHThe same idea shows up in counting. count(*) over the student table is 4, but count(dept_id) is 3, because it skips the NULL.
07Common interview questions
Q: Difference between INNER and LEFT JOIN?
A: Inner keeps only matched rows. Left keeps every left row and gives NULLs on the right where nothing matched.
Q: How do you find rows in A that have no match in B?
A: Left join B and filter where a column of B is NULL, or use NOT EXISTS. Avoid NOT IN when the subquery column can be NULL.
Q: What is a self join?
A: Joining a table to itself with two aliases, for example employees to their managers in the same table.
Q: What does a join cost without an index?
A: A naive nested loop compares every row to every row, O(n times m). With an index on the join column, each lookup is O(log m). Engines also use hash joins and merge joins.
Q: Why did my left join return fewer rows than expected?
A: Almost always a WHERE condition on the right table. Move it into ON.