EXISTS Operator

Overview

The EXISTS operator tests whether a subquery returns any rows, returning TRUE as soon as the first matching row is found.

Learning Objectives

• Use EXISTS to write subqueries that test for row presence.
• Explain why EXISTS short-circuits execution.
• Compare EXISTS performance against IN.

Detailed Concept Explanation

EXISTS accepts a subquery as its argument:

• It returns TRUE if the subquery returns at least one row.
• It returns FALSE if the subquery returns zero rows.

Short-Circuit Evaluation

EXISTS doesn't care what columns the subquery selects—it only checks for the existence of matching rows. As soon as the database engine finds a single matching row, it stops searching (short-circuits) and returns TRUE. By convention, developers write SELECT 1 inside EXISTS subqueries.

Code Examples

SQL

-- Find departments that have at least one employee earning over $100k
SELECT d.department_id, d.dept_name
FROM departments d
WHERE EXISTS (
  SELECT 1 
  FROM employees e 
  WHERE e.department_id = d.department_id 
    AND e.salary > 100000
);

Best Practices

• Use SELECT 1: Write SELECT 1 inside EXISTS (...) subqueries. It clearly signals that only row presence matters, not column values.

Interview Perspective

Interactive Challenges

Summary

EXISTS checks whether a subquery returns at least one row, short-circuiting as soon as a match is found.