Learn LEFT JOIN: THE DEEP

THE DEEP is chapter 9 and the LEFT JOIN chapter. An INNER JOIN would silently drop the very rows you are hunting, so you join OPERATIVES to ASSIGNMENTS with LEFT JOIN, keeping every operative whether or not a matching assignment exists.

The unmatched rows come back with NULL in the assignment columns, and filtering on IS NULL exposes the phantoms. This find-the-missing-rows pattern, sometimes called an anti-join, shows up constantly in real audits and in data interviews.

Two missions, one table pair, and a habit worth keeping: always ask which rows an inner join would lose.

This is the shape of query you practice here, not the answer to any mission. The real stages come with their own data, objectives, and hints.

SELECT OPERATIVES.CODENAME
FROM OPERATIVES
LEFT JOIN ASSIGNMENTS ON OPERATIVES.ID = ASSIGNMENTS.OPERATIVE_ID
WHERE ASSIGNMENTS.OPERATIVE_ID IS NULL;

Free guides that pair well with this chapter:

• Subqueries and EXISTS: Correlated vs Non-Correlated
• SQL Joins Explained: INNER, LEFT, RIGHT, FULL