A SQL join combines rows from two tables using a related column. INNER JOIN keeps only matching rows; LEFT JOIN keeps every row from the left table and fills missing matches with NULL; RIGHT JOIN does the same from the right; FULL OUTER JOIN keeps everything from both; CROSS JOIN pairs every row with every row; and a SELF JOIN joins a table to itself.

Why do databases split data into tables?

Relational databases store each kind of thing once: students in one table, courses in another, enrolments in a third. This avoids repeating the same student's name and email in every enrolment row, which wastes space and causes inconsistencies when details change. The process is called normalisation. Joins are how you put the pieces back together when you query.

What example data will we use?

Two small tables:

students
idnamecity
1AshaKolkata
2RaviPune
3MeeraPatna
submissions
idstudent_idtaskscore
101Quiz 118
111Quiz 216
122Quiz 112
134Quiz 115

Meera (id 3) has no submissions, and submission 13 belongs to a student id 4 who isn't in the students table (perhaps a deleted account).

What does each join return?

INNER JOIN: SELECT s.name, sub.task FROM students s INNER JOIN submissions sub ON sub.student_id = s.id; returns Asha/Quiz 1, Asha/Quiz 2 and Ravi/Quiz 1. Meera and the orphan submission disappear.

LEFT JOIN (students on the left) returns the three matches plus Meera with NULL task. Use it for questions like "list every student and their submissions, including those who submitted nothing".

RIGHT JOIN returns the three matches plus the orphan submission with a NULL name. The same result comes from a LEFT JOIN with the tables swapped.

FULL OUTER JOIN returns all five: matches, Meera and the orphan. (MySQL doesn't support it directly; combine a LEFT and RIGHT join with UNION.)

CROSS JOIN pairs every student with every submission: 3 × 4 = 12 rows. Useful for generating combinations, such as every student × every upcoming test date.

JoinRows returned hereTypical question
INNER3Who submitted, and what?
LEFT4Every student, with submissions if any
RIGHT4Every submission, with student if known
FULL OUTER5Reconcile two lists completely
CROSS12All combinations
Rows each join returns on the example tables
INNER3
LEFT4
RIGHT4
FULL OUTER5
CROSS12

Counted from the example above.

What is a self join?

A self join joins a table to itself using two aliases. Classic example: an employees table with a manager_id column pointing to another employee. SELECT e.name, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id; lists each employee with their manager's name. Self joins also find pairs, such as students from the same city.

How do you find rows with no match?

An "anti join" answers questions like "which students have never submitted?". Use a LEFT JOIN and keep rows where the right side is NULL: ... LEFT JOIN submissions sub ON sub.student_id = s.id WHERE sub.id IS NULL; returns Meera. NOT EXISTS with a subquery does the same and is often clearer. This pattern is one of the most frequently asked in SQL interviews.

What mistakes do people make with joins?

  • Accidental duplication. Joining students to submissions and then to attendance multiplies rows: two submissions × ten attendance rows = twenty rows per student, inflating sums. Aggregate each table first, then join.
  • Filtering a LEFT JOIN in WHERE. WHERE sub.score > 10 removes the NULL rows and silently turns the LEFT JOIN into an INNER JOIN. Put the condition in the ON clause if you want to keep unmatched rows.
  • Joining on the wrong column or mismatched types (text vs integer), which can return nothing or force slow conversions.
  • Forgetting NULL rules. NULL never equals NULL in SQL; rows with NULL keys won't match in a normal join.
  • SELECT * across joins, which returns ambiguous duplicate column names. Name the columns you need.

How do joins perform on large tables?

Databases choose among three main strategies: nested loop (good when one side is small and the other has an index), hash join (builds a hash table on the smaller side; great for large unsorted inputs), and merge join (both sides sorted on the key). You can see which one is used with EXPLAIN. The practical rules: index foreign-key columns, filter rows as early as possible, select only needed columns, and check the plan when a query is slow. Our guide to indexing and transactions explains why indexes help so much.

Where are joins used in data science and AI?

Almost every machine learning dataset is built with joins: customers joined to transactions, students joined to attendance and scores, products joined to reviews. Getting joins wrong silently corrupts training data, for example by duplicating rows or leaking future information into features. That is why SQL comes before Pandas and machine learning in most serious learning paths. Pandas' merge() uses exactly the same join types.

How do joins look in a real analytics query?

A typical business question: "For each city, how many students submitted at least one quiz, and what was their average best score?" It needs several steps. First aggregate submissions to one row per student (their best score) with GROUP BY. Then join that result to students with an INNER JOIN, because we only want students who submitted. Finally group by city and compute the count and average. Writing it with a common table expression (a WITH clause) keeps each step readable:

WITH best AS (SELECT student_id, MAX(score) AS best FROM submissions GROUP BY student_id) SELECT s.city, COUNT(*) AS students, AVG(b.best) AS avg_best FROM students s JOIN best b ON b.student_id = s.id GROUP BY s.city;

Notice the order: aggregate first, then join. Joining raw submissions to students and averaging would weight students with more submissions more heavily, a subtle bug that produces plausible but wrong numbers. Good SQL is mostly about thinking through what one row means at each step.

What is the difference between JOIN conditions and WHERE filters?

For INNER JOIN, putting a condition in ON or WHERE gives the same result. For outer joins it does not. A condition in the ON clause decides which rows match while still keeping unmatched left rows; a condition in WHERE runs after the join and throws away rows that fail it, including the NULL rows the outer join was meant to keep. When a LEFT JOIN seems to lose rows, this is almost always the reason. Writing the condition where it belongs is one of the clearest signs of SQL experience that interviewers look for.

How should you practise joins?

  1. Create the two tables above in SQLite or PostgreSQL and run every join.
  2. Predict the row count before running each query, then check.
  3. Write anti joins for "never", "missing" and "unmatched" questions.
  4. Join three tables and catch a duplication bug on purpose, then fix it by aggregating first.

Program Zero spends five weeks on databases, from design and joins to indexing, transactions and NoSQL. See the SQL and database course page and the database phase in the curriculum.