All three can express "give me rows from A that have a match in B," and they read almost interchangeably, but they aren't always equivalent in performance or NULL behavior:
-- EXISTS: stops as soon as it finds one matching row per outer row
SELECT * FROM Employees e
WHERE EXISTS (SELECT 1 FROM Departments d WHERE d.dept = e.dept);
-- IN: builds the full result list from the subquery, then checks membership
SELECT * FROM Employees e
WHERE e.dept IN (SELECT dept FROM Departments);
-- JOIN: usually the most natural if you also want columns from the other table
SELECT e.* FROM Employees e
JOIN Departments d ON d.dept = e.dept;
The genuinely important difference is NULL behavior with NOT IN, which is a real, sharp footgun:
-- If Departments.dept contained even one NULL, this returns ZERO rows —
-- not "employees whose dept isn't in Departments", but nothing at all
SELECT * FROM Employees e
WHERE e.dept NOT IN (SELECT dept FROM Departments);
-- NOT EXISTS has no such trap — it's safe regardless of NULLs in the subquery
SELECT * FROM Employees e
WHERE NOT EXISTS (SELECT 1 FROM Departments d WHERE d.dept = e.dept);
NOT IN (list containing NULL) fails silently because x NOT IN (1, 2, NULL) expands to x <> 1 AND x <> 2 AND x <> NULL — and x <> NULL is UNKNOWN, which poisons the entire AND chain to UNKNOWN (recall the three-valued-logic table above: UNKNOWN AND anything is never TRUE) regardless of how the other comparisons came out. This one behavior alone is a good reason to prefer NOT EXISTS over NOT IN whenever the subquery's column could conceivably contain a NULL — and since it costs nothing to always prefer it, many style guides do so unconditionally.