intermediate

EXISTS Operator

By DataPlayArena Technical Review BoardUpdated 2026-07-236 min readRuntime Verified

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

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

Note

Interview Question: Does SELECT * inside EXISTS affect query performance?

No. Database query optimizers ignore the select projection list inside EXISTS subqueries because they only test for row existence. However, writing SELECT 1 is standard practice for readability.


Interactive Challenges

Challenge 1: Check Active Departments

Select 'dept_name' from 'departments' d where EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id).


Summary

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

DPA

DataPlayArena Technical Editorial Board

Curated & maintained by enterprise data platform engineers with 10+ years in distributed systems.

Runtime Verification Standards: Every code example, query, and architecture diagram in this tutorial is compiled and verified against real runtimes (Apache Spark 3.5, Python 3.11, PostgreSQL, and DuckDB). If you spot any errata or want to suggest improvements, please submit feedback.