EXISTS

EXISTS tests whether a subquery returns at least one row, returning true or false — it doesn't care how many rows or what values, only whether any row matches, which lets the database stop scanning at the first match.

EXISTS is almost always used with a correlated subquery: WHERE EXISTS (SELECT 1 FROM job_history jh WHERE jh.employee_id = e.employee_id) finds employees who have at least one job history row. NOT EXISTS is the standard, NULL-safe way to express "has none of" — safer than NOT IN, which silently returns no rows at all if the subquery's result contains any NULL.

Because the database can stop as soon as it finds one matching row, EXISTS is often faster than an equivalent IN subquery on large tables.

SELECT first_name FROM employees e
WHERE EXISTS (
  SELECT 1 FROM job_history jh WHERE jh.employee_id = e.employee_id
);