Subquery with EXISTS operator in SQL

Introduction

Use EXISTS to check if some related data exists without returning the data itself. It helps answer yes/no questions about data presence.

You want to find customers who have placed at least one order.

You want to check if a product has any reviews before showing it.

You want to list employees who manage at least one team member.

You want to filter records only if related records exist in another table.

Syntax

SQL

SELECT columns
FROM table1
WHERE EXISTS (
  SELECT 1
  FROM table2
  WHERE condition
);

EXISTS returns TRUE if the subquery finds any rows, otherwise FALSE.

The subquery usually checks for related rows using a condition linking the two tables.

Examples

Finds customers who have at least one order.

SQL

SELECT name
FROM customers
WHERE EXISTS (
  SELECT 1
  FROM orders
  WHERE orders.customer_id = customers.id
);

Lists products that have at least one review.

SQL

SELECT product_name
FROM products
WHERE EXISTS (
  SELECT 1
  FROM reviews
  WHERE reviews.product_id = products.id
);

Finds employees who manage others.

SQL

SELECT employee_name
FROM employees e
WHERE EXISTS (
  SELECT 1
  FROM employees
  WHERE manager_id = e.id
);

Sample Program

This query lists customers who have placed at least one order. Alice and Carol have orders, Bob does not.

SQL

CREATE TABLE customers (id INT, name VARCHAR(50));
CREATE TABLE orders (id INT, customer_id INT);

INSERT INTO customers VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Carol');
INSERT INTO orders VALUES (101, 1), (102, 1), (103, 3);

SELECT name
FROM customers
WHERE EXISTS (
  SELECT 1
  FROM orders
  WHERE orders.customer_id = customers.id
);

Important Notes

EXISTS stops checking as soon as it finds the first matching row, so it is efficient.

The subquery inside EXISTS does not return data to the main query, only TRUE or FALSE.

You can use NOT EXISTS to find records without related rows.

Summary

EXISTS checks if related rows exist and returns TRUE or FALSE.

Use EXISTS to filter rows based on presence of related data.

It is efficient because it stops searching after finding the first match.