← Blog

SQL JOINs Explained: The Suspect With No Record

How SQL JOINs really work and why the row that goes missing is the one that matters.

You joined two tables. You got zero rows back. No error, no warning, just an empty result where you expected a list of suspects.

SELECT COUNT(*)
FROM suspect_info s
INNER JOIN events e ON e.suspect_id = s.id
WHERE s.case_id = 2;

0

The tables aren't empty. Case 2 has suspects and it has events. You joined them on e.suspect_id = s.id, which looks completely correct. You got nothing.

That silence is the most confusing thing about JOINs. When a join returns no rows, it usually isn't broken. It's telling you something true that you didn't ask clearly enough to hear. Here it's telling you that in Case 2, events.suspect_id is empty. We'll come back to that once the groundwork is down.

New to SQL entirely? Start with the interactive cases, then come back for this.

The one-line answer

A JOIN stitches rows from two tables together wherever a column in one matches a column in the other. INNER JOIN keeps only the rows that found a match. LEFT JOIN keeps every row from the first table, matched or not. It fills the blanks with NULL where nothing lined up.

That difference, matched-only versus keep-everything, is the whole game. INNER hides the suspects with no record. LEFT is how you find them. And in a murder case, the one with no record is often exactly who you want.

The evidence board

Picture a detective's evidence board.

suspect_info is the stack of suspect files. One card per person, with their id, name, role, relation, location and alibi_time.

events is the log of what happened. Each row is an event_type tied to a suspect by suspect_id and stamped with an event_timestamp.

cases is thin. Just an id and a name, one row per novel.

A JOIN is the string you pin between cards. There are two useful pieces of string on this board. suspect_info.case_id = cases.id connects each suspect to their novel. events.suspect_id = suspect_info.id connects each suspect to the things they did.

INNER JOIN shows only the files with string attached to something. LEFT JOIN keeps every file on the board, including the ones with nothing pinned to them. Those bare files, the people nothing connects to, are where a detective's eye goes first.

INNER JOIN: no match, no row

Start with the string that always holds. Every suspect belongs to a case, so joining suspects to cases loses nobody.

SELECT s.name, s.role, c.name AS novel
FROM suspect_info s
INNER JOIN cases c ON c.id = s.case_id
WHERE s.case_id = 1
ORDER BY s.name;

Each suspect comes back with the novel they belong to: Antonio Foscarelli | Passenger | Murder on the Orient Express and so on down the manifest. Clean, because every case_id on a suspect file matches a real row in cases. No string is left dangling.

Now point the join at events instead and ask a different question. How many events does each suspect in Case 4 have on file?

SELECT s.name, COUNT(e.id) AS events
FROM suspect_info s
INNER JOIN events e ON e.suspect_id = s.id
WHERE s.case_id = 4
GROUP BY s.name
ORDER BY s.name;

Nine rows come back. Case 4, Murder in Mesopotamia, has ten people. The INNER JOIN just dropped one of them and said nothing about it. The missing person didn't fail a filter and didn't throw an error. They simply had no matching row in events, so the inner join had no string to pin and quietly left them off the board.

That is INNER JOIN's entire personality. No match, no row. Usually helpful. Occasionally it hides the exact person you needed to see.

INNER JOIN vs LEFT JOIN: who gets dropped

Change one word. INNER becomes LEFT. Here's the same question as a left join example, nothing else touched.

SELECT s.name, COUNT(e.id) AS events
FROM suspect_info s
LEFT JOIN events e ON e.suspect_id = s.id
WHERE s.case_id = 4
GROUP BY s.name
ORDER BY s.name;

Ten rows now. Everyone from suspect_info stays, whether or not they have events pinned to them. The tenth row is the one INNER was hiding.

Louise Leidner, with an event count of 0.

She has a file. She has a name, a role, a place on the manifest. What she doesn't have is a single event on the log. Nothing she did, nowhere she went, no timestamp. In Murder in Mesopotamia, there's a reason for that. Louise Leidner is the victim. The one person with nothing on file is the woman who was killed.

That is INNER JOIN vs LEFT JOIN stated as plainly as it goes. INNER answers "who did things and what." LEFT answers "who's here, including the ones who did nothing." A murder investigation lives in that second question. The silent row, the file with no string, the suspect with no record, that isn't noise to filter out. That's the case.

I learned this one the slow way. Early on I built a weekly report that joined customers to their orders with a plain JOIN. For about a month nobody questioned it, least of all me. Then someone asked why our count of active customers was so much lower than the signup numbers. The customers who hadn't ordered anything yet weren't inactive. They just weren't in the report. I changed one word to LEFT and they all walked back in.

Keep that instinct. Next time a report looks complete and something still feels off, swap your INNER for a LEFT and see who walks back into the room.

Types of JOINs in SQL: RIGHT, FULL OUTER and CROSS

You've met the two that carry most of the work. Three more round out the family.

RIGHT JOIN is LEFT JOIN with the tables flipped. It keeps every row from the right-hand table instead of the left. Anything LEFT can do, RIGHT can do by swapping the table order, which is why most people pick LEFT and stay there. PostgreSQL supports both.

FULL OUTER JOIN keeps the unmatched rows from both sides at once. Every suspect with no events and every event with no matching suspect, all in one result, blanks filled with NULL. When you need every loose end on the board regardless of which side it's dangling from, that's the one. PostgreSQL supports it.

CROSS JOIN pins every file to every event, with no matching condition at all.

SELECT COUNT(*)
FROM suspect_info s CROSS JOIN events e
WHERE s.case_id = 4 AND e.case_id = 4;

110

Ten suspects, eleven events, every possible pairing. 110 rows. Once in a while that's genuinely what you want. Far more often, as you're about to see, it's a join gone wrong.

Three mistakes and the fix for each

1. Joining on a key full of NULLs, so the SQL join returns no rows.

Back to the query that opened this article.

SELECT COUNT(*)
FROM suspect_info s
INNER JOIN events e ON e.suspect_id = s.id
WHERE s.case_id = 2;

0

SELECT COUNT(*)
FROM suspect_info s
INNER JOIN events e ON e.suspect_name = s.name
WHERE s.case_id = 2;

4

Same two tables, a column that has real values, four matches. When a join returns zero rows and you're sure the data exists, suspect your join key before anything else.

2. A filter on the right-hand table that quietly kills your LEFT JOIN.

You wrote a LEFT JOIN to keep everyone, then added what looked like a harmless filter.

SELECT s.name, COUNT(e.id) AS events
FROM suspect_info s
LEFT JOIN events e ON e.suspect_id = s.id
WHERE s.case_id = 4 AND e.case_id = 4
GROUP BY s.name
ORDER BY s.name;

SELECT s.name, COUNT(e.id) AS events
FROM suspect_info s
LEFT JOIN events e ON e.suspect_id = s.id AND e.case_id = 4
WHERE s.case_id = 4
GROUP BY s.name
ORDER BY s.name;

Ten rows again, Louise included. In the ON clause the condition is part of how the string gets pinned. In WHERE it's a filter applied after the join. It doesn't spare the NULLs. If you want the full story on when each clause runs, the order of execution and WHERE vs HAVING pieces cover it.

3. A missing join condition, which turns into an accidental cross join.

SELECT COUNT(*)
FROM suspect_info s, events e
WHERE s.case_id = 4 AND e.case_id = 4;

110

SELECT COUNT(*)
FROM suspect_info s, events e
WHERE s.case_id = 4 AND e.case_id = 4 AND e.suspect_id = s.id;

11

Add the join key and the 110 collapses back to the 11 real pairings, each suspect matched only to their own events. When a result is suspiciously huge and every number looks multiplied, count your join conditions first.

SQL JOINs explained: the quick reference

Screenshot that. Nearly every JOIN that surprises you is one of these five behaving exactly as written.

FAQ

What's the difference between INNER JOIN and LEFT JOIN?

INNER JOIN keeps only rows that match on both sides, while LEFT JOIN keeps every row from the left table and fills in NULLs where the right side has no match. So INNER can silently drop rows that have no partner and LEFT shows them to you instead. When you care about what's missing, reach for LEFT.

Is JOIN the same as INNER JOIN?

Yes. Written on its own, JOIN means INNER JOIN in PostgreSQL and every other major database. The INNER keyword is optional and there purely for readability. If you see a bare JOIN, read it as inner.

Does table order matter in a LEFT JOIN?

Yes. LEFT JOIN keeps every row from the table on the left of the keyword, so swapping the two tables changes which side is preserved. A LEFT JOIN B keeps all of A; B LEFT JOIN A keeps all of B. For INNER JOIN, order doesn't change the result.

Why does my JOIN return duplicate rows?

Because one row on one side matches several rows on the other. In Case 4, Amy Leatheran and Dr Eric Leidner each have two events, so a join to events returns two rows for each of them, one per event. That isn't a bug, it's one row per match. Group or aggregate if you want them collapsed back to one.

Close the case

JOINs aren't really about combining tables. They're about deciding who stays on the board and who falls off it and whether you notice when someone does. INNER keeps the connected. LEFT keeps everyone, which in a murder case means it keeps the victim.

Case 4 is open. Louise Leidner is still on the manifest with nothing on her file. Go find out why. New to all this? Start with the SQL basics, then take the case.

Start Case 4 →