Ghost in the SQL Data SQL JOIN Guide: Connect Evidence Across Tables

Most cases in Ghost in the SQL Data don't fall apart because you wrote a bad filter — they fall apart because the clues live in different tables and never get connected. That's exactly why a solid Ghost in the SQL Data SQL JOIN guide matters: JOIN is the clause that turns isolated records into a real evidence chain. In this Ghost in the SQL Data SQL JOIN guide, you'll learn how keys link the People, Events, Locations, and Cases tables, how to write joins that actually return the right rows, and how to fix the mistakes that leave detectives staring at empty results.

Why JOIN Is the Make-or-Break Skill in Ghost in the SQL Data

Ghost in the SQL Data is a story-driven detective game built on real SQL. You investigate Paris emergency-call records, filter evidence, and present conclusions on an investigator board. Early cases lean on SELECT and WHERE, but the difficulty curve eventually pushes you into cross-table reasoning — connecting a person to an event, an event to a location, and a location to a case.

That's where JOIN becomes unavoidable. A single table rarely answers the full question. If you only know a name, you need the People table. If you need a timeline, you need Events. If you need to know where something happened, you need Locations. JOIN is how those pieces meet.

Community reports from the game's wiki consistently describe "cross-table connection" as an intermediate-to-advanced investigation pattern — one that demonstrates relational database reasoning rather than simple filtering. Treat this guide as your bridge from basic queries to that level.

The One-Sentence Rule

Before writing any JOIN, ask: what shared identifier links these two tables? If you can't answer that in one sentence, you're not ready to write the query yet.

The Ghost in the SQL Data Schema: Which Tables Join to Which

You can't write a JOIN without knowing the keys. Here's how the core investigation tables relate, based on the game's published schema.

The critical insight: Events is the hub table. It carries both person_id and location_id, which means almost every meaningful investigation join passes through Events.

Why the Shared Key Must Match Exactly

The wiki's troubleshooting section flags this specific failure: joining People.name to Events.event_id returns duplicates or nothing at all. Names are text; event IDs are integers. They will never match. Always join identifier to identifier.

How to Write a JOIN Query Step by Step

Here's a repeatable workflow that works for every case in the game.

A Working Three-Table Example

SELECT p.name, e.event_date, l.location_name
FROM People p
JOIN Events e ON p.person_id = e.person_id
JOIN Locations l ON e.location_id = l.location_id
WHERE l.city = 'Paris'
ORDER BY e.event_date;

Read that aloud as a sentence: "Show me each person's name, the event date, and the location name, for events in Paris, sorted by date." If your query doesn't read as a sentence, it probably won't return the evidence you expect.

Use Table Aliases From the Start

Aliases (p, e, l) aren't cosmetic. Once two tables share a column name — and person_id appears in both People and Events — unqualified column references become ambiguous and the query fails. Aliasing forces you to be explicit about which table each column comes from, which is exactly the discipline this game rewards.

JOIN Types and When Each One Helps Your Investigation

Most players only need the default INNER JOIN, but understanding the alternatives prevents a lot of confusion when results look wrong.

The LEFT JOIN Trick for Missing Evidence

Absence of evidence is evidence. If a person appears in People but has zero matching rows in Events, a LEFT JOIN with a WHERE e.event_id IS NULL filter surfaces them instantly. In a mystery game, "this person has no recorded activity" is often the clue that cracks the case.

Five JOIN Mistakes That Break Ghost in the SQL Data Cases

The wiki's query error fixes section documents the most common failures. Here's the JOIN-specific version, framed for investigators.

The Cartesian Product Trap

If you forget the ON clause, SQL pairs every row in one table with every row in the other. A 50-row People table joined to a 200-row Events table returns 10,000 rows. If your result set suddenly explodes, check your ON clause first — that's almost always the culprit.

Filter Placement Matters

A subtle but important habit: relationship conditions belong in ON, and investigative conditions belong in WHERE. Mixing them up changes which rows survive the join, and in a LEFT JOIN it can quietly convert your query back into an inner join.

Putting JOIN to Work: Case Patterns and Practice Tips

Different case types demand different join strategies. Here's how the game's mission patterns map to query approaches.

Build Incrementally

Start with the base table and a simple SELECT. Add the first JOIN and run it. Add the second JOIN and run it. Each intermediate result teaches you something about the data. Players who write a five-line query all at once and hit "run" usually can't tell which clause broke.

Verify Before You Conclude

Query results are evidence, not verdicts. If a three-table join returns six suspects, that's a candidate list — not an answer. Re-read the case objective, check whether every required condition is satisfied, and narrow further with WHERE before you commit on the investigator board.

Use the Hint Tiers Wisely

The game's hint system escalates from a light objective hint to a relationship hint to complete reasoning. When you're stuck on a join, the relationship hint is the one that points at the shared key. Try one more query attempt before revealing full reasoning — the retention is much better when you find the key yourself.

For broader query fundamentals beyond joins, the official Ghost in the SQL Data wiki SQL query guide is a solid companion resource, and the game is available on Steam if you want to test these techniques in the demo first.

Frequently Asked Questions

Do I need to know SQL JOIN syntax before playing Ghost in the SQL Data?

No. Guided mode introduces concepts progressively, starting with SELECT and WHERE before moving to joins and compound filters. That said, reading a Ghost in the SQL Data SQL JOIN guide like this one before you reach the cross-table cases will save you a lot of trial and error.

Which tables should I join first in a typical investigation?

Start with People and Events, since person_id links them directly. Add Locations through location_id only when the case question involves a place. Adding tables you don't need makes results harder to interpret.

Why does my JOIN return duplicate rows?

Two common causes: joining through a non-unique field, or joining the wrong columns entirely (like name to event_id). Confirm you're matching identifier to identifier, and check whether one person legitimately has multiple events — that's not a duplicate, it's a timeline.

Is the JOIN skill in this game useful outside of it?

Yes. Filtering, sorting, grouping, and joining tables are core SQL fundamentals. The thinking you practice in Ghost in the SQL Data transfers directly to real database work, which is part of why the game appeals to both beginners and experienced analysts.

Once JOIN clicks, the whole investigation database opens up. Start with the two-table pattern, read every query as a sentence, and let the shared keys guide you from one piece of evidence to the next.