Practice · Core CS simulator

SQL joins, row by row

Pick a join and see which rows match and where the NULLs come from. Edit the tables to try your own case. Every result here is what a real database (SQLite) returns.

SELECT *
FROM Students s
LEFT JOIN Departments d
  ON s.dept_id = d.id;

5 rows. Every student, matched where possible. Students with no matching department get NULLs on the right.

s.ids.names.dept_idd.idd.name
1Asha1010CSE
2Ravi2020ECE
3Meera1010CSE
4KabirNULLNULLNULL
5Zoya40NULLNULL

The tables (edit them)

Joined on Students.dept_id = Departments.id (the highlighted columns). Leave a cell empty for NULL. ✓ marks rows that found a match.

Students
idnamedept_idMatched
✓
✓
✓
–
–
Departments
idnameMatched
✓
✓
–

Every join, same tables

INNERLEFTRIGHTFULL OUTERCROSS
3 rows5 rows4 rows6 rows15 rows

What each join keeps

  • INNER JOIN: only the pairs that match.
  • LEFT JOIN: every row of the left table; NULLs where the right table has no match.
  • RIGHT JOIN: every row of the right table; NULLs where the left table has no match.
  • FULL OUTER JOIN: everything from both sides, matched where possible.
  • CROSS JOIN: every pair, with no condition: rows × rows.

How many rows?

  • A row that matches three rows on the other side appears three times: joins can multiply rows.
  • INNER is never more than LEFT or RIGHT, and FULL = LEFT + RIGHT − INNER.

NULL never matches

In SQL, NULL = NULL is not true, so a NULL key never joins, not even to another NULL. That's why a student whose dept_id is NULL never gets a department, even in a LEFT JOIN: they appear, but with NULLs on the right.

Asked in interviews

  • Students with no department? LEFT JOIN, then WHERE d.id IS NULL. This is called an anti-join.
  • ON or WHERE in a LEFT JOIN? A condition on the right table in WHERE removes the NULL rows and turns it into an inner join. Put it in ON to keep every left row.
  • FULL OUTER JOIN in MySQL? MySQL doesn't have it: combine a LEFT JOIN and a RIGHT JOIN with UNION.

More: the DBMS questions, answered, and the lesson on database indexes.