Joins are easiest to understand with tables small enough to hold in your head. We will use two, build the results by hand, and then break things on purpose. No Venn diagrams; just rows.
The data
An employees table with four rows, where the last column is the department each person belongs to:
| emp_id | name | dept_id |
|---|---|---|
| 1 | Anita | 10 |
| 2 | Bala | 20 |
| 3 | Charu | NULL |
| 4 | Dev | 30 |
And a departments table with three rows:
| dept_id | dept_name |
|---|---|
| 10 | Finance |
| 20 | Engineering |
| 40 | Legal |
Notice the deliberate gaps. Charu has no department. Dev points to department 30, which does not exist. Legal (40) has nobody in it.
Step 1: the inner join, done like a database would
An inner join on dept_id means: take each employee row, look for department rows whose dept_id is equal, and emit a combined row for each match. Rows without a match disappear.
Anita (10) finds Finance: one output row. Bala (20) finds Engineering: one row. Charu has NULL. In SQL, NULL is not equal to anything, not even another NULL, so she finds no match and drops out. Dev (30) finds nothing and drops out. Legal never gets used. Result: two rows, Anita with Finance and Bala with Engineering.
If you were building a headcount report and used an inner join, you would report two employees when you have four. Nothing errors. The query is simply answering a narrower question than you meant.
Step 2: the left join keeps the left side whole
A left join says: keep every row from the left table (employees here), and attach matching department data where it exists. Where no match exists, fill the department columns with NULL.
Now all four employees appear. Anita and Bala look as before. Charu appears with a NULL department name. Dev also appears with NULL, because department 30 is missing. Four rows out, four rows in.
This also gives you a useful trick. Filter a left join to rows where the right side’s key is NULL, and you get exactly the employees with no matching department: Charu and Dev. Data teams use this to find orphans, such as orders whose customer record is gone. One caution: Charu and Dev are “missing” for different reasons (no department assigned versus a dangling reference), and the join cannot tell you that without looking at the original column.
Reverse the roles, with departments on the left, and Legal appears with no employees. Some databases also offer a right join and a full outer join (which keeps unmatched rows from both sides), but a right join is just a left join with the tables swapped, and many teams standardise on left joins for readability. MySQL, notably, has no full outer join keyword, so people combine two left joins.
Step 3: break it, rows multiply
Suppose someone loads a second row into departments: dept_id 20 again, named “Engineering (Old)”. Perhaps a bad import; perhaps a history table without an effective-date filter. Repeat the left join. Bala now matches two department rows, so he appears twice. Four employees become five rows.
That is the rule behind most “why are my numbers inflated” bugs: a join returns one output row per matching pair. If the right side is not unique on the join column, the left side’s rows repeat. Sum salaries after that join and Bala’s pay is counted twice. When you join, always know whether the key is unique on each side. A one-to-one or many-to-one join keeps the row count; a one-to-many join grows it, and that may be exactly what you want, such as listing each order line. The point is to expect it.
Step 4: the filter that silently undoes your left join
Say you want all employees, and for those in Finance show the department name. You left join, then add a filter in the WHERE clause saying the department name equals Finance. The filter is applied after the join. Charu’s and Dev’s department name is NULL, and NULL equals nothing, so they fail the filter and vanish. Your left join has quietly turned into an inner join, and even Bala is gone.
If the condition is meant to limit which right-hand rows can attach, put it in the join condition itself, next to the key match. Then employees outside Finance still appear, with NULL department data. If the condition is meant to limit the final rows, WHERE is correct. They look similar and behave differently, but only for outer joins; for inner joins the two placements give the same result.
A quick self-check
Before trusting any join result, compare counts. Count the rows of the left table. Count the rows of the join. If you used a left join and the number went down, something filtered it. If it went up, the right side is not unique. Doing this takes ten seconds and catches most join bugs before a report reaches a manager.
Figures and names in the examples are illustrative.
Leave a Reply