What is the EXISTS Operator?
The EXISTS operator checks whether a subquery returns at
least one row. If the subquery returns one or more rows, EXISTS evaluates
to true. If it returns no rows, EXISTS evaluates to false.
Basic EXISTS Syntax
EXISTS is normally used with a subquery inside a WHERE clause. The subquery determines whether matching records exist in another table.
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS orders;
CREATE TABLE customers (
customer_id INTEGER,
customer_name TEXT
);
CREATE TABLE orders (
order_id INTEGER,
customer_id INTEGER,
amount INTEGER
);
INSERT INTO customers VALUES
(1, 'Aarav'),
(2, 'Meera'),
(3, 'Kabir'),
(4, 'Riya');
INSERT INTO orders VALUES
(101, 1, 900),
(102, 1, 1200),
(103, 3, 700);
SELECT customer_id, customer_name
FROM customers AS c
WHERE EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.customer_id
)
ORDER BY customer_id;EXISTS with a WHERE Condition
The subquery can contain additional conditions. In this example, only customers with an order greater than 1000 are returned.
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS purchases;
CREATE TABLE customers (
customer_id INTEGER,
customer_name TEXT
);
CREATE TABLE purchases (
purchase_id INTEGER,
customer_id INTEGER,
amount INTEGER
);
INSERT INTO customers VALUES
(1, 'Nisha'),
(2, 'Rohan'),
(3, 'Tara'),
(4, 'Dev');
INSERT INTO purchases VALUES
(201, 1, 450),
(202, 1, 1500),
(203, 2, 600),
(204, 3, 2200);
SELECT customer_id, customer_name
FROM customers AS c
WHERE EXISTS (
SELECT 1
FROM purchases AS p
WHERE p.customer_id = c.customer_id
AND p.amount > 1000
)
ORDER BY customer_id;EXISTS with Employee Records
EXISTS can be used to find employees who have related records in another table.
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS projects;
CREATE TABLE employees (
employee_id INTEGER,
employee_name TEXT,
department TEXT
);
CREATE TABLE projects (
project_id INTEGER,
employee_id INTEGER,
project_name TEXT
);
INSERT INTO employees VALUES
(1, 'Anaya', 'Design'),
(2, 'Vikram', 'Engineering'),
(3, 'Mira', 'Marketing'),
(4, 'Karan', 'Finance');
INSERT INTO projects VALUES
(501, 1, 'Website Redesign'),
(502, 2, 'Mobile App'),
(503, 2, 'API Upgrade');
SELECT employee_id, employee_name, department
FROM employees AS e
WHERE EXISTS (
SELECT 1
FROM projects AS p
WHERE p.employee_id = e.employee_id
)
ORDER BY employee_id;NOT EXISTS
NOT EXISTS returns true when the subquery returns no rows.
It is useful for finding records that do not have a related record.
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS orders;
CREATE TABLE customers (
customer_id INTEGER,
customer_name TEXT
);
CREATE TABLE orders (
order_id INTEGER,
customer_id INTEGER,
amount INTEGER
);
INSERT INTO customers VALUES
(1, 'Ishita'),
(2, 'Arjun'),
(3, 'Neha'),
(4, 'Dev');
INSERT INTO orders VALUES
(301, 1, 800),
(302, 2, 1200);
SELECT customer_id, customer_name
FROM customers AS c
WHERE NOT EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.customer_id
)
ORDER BY customer_id;EXISTS with Multiple Conditions
Multiple conditions can be used inside an EXISTS subquery with
AND and other SQL operators.
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS sales;
CREATE TABLE products (
product_id INTEGER,
product_name TEXT,
category TEXT
);
CREATE TABLE sales (
sale_id INTEGER,
product_id INTEGER,
quantity INTEGER
);
INSERT INTO products VALUES
(1, 'Keyboard', 'Accessories'),
(2, 'Monitor', 'Displays'),
(3, 'Mouse', 'Accessories'),
(4, 'Webcam', 'Accessories');
INSERT INTO sales VALUES
(401, 1, 5),
(402, 2, 2),
(403, 3, 12);
SELECT product_id, product_name
FROM products AS p
WHERE EXISTS (
SELECT 1
FROM sales AS s
WHERE s.product_id = p.product_id
AND s.quantity >= 10
)
ORDER BY product_id;EXISTS with Different Tables
EXISTS can check whether related data is available in another table, even when the two tables contain different types of information.
DROP TABLE IF EXISTS authors;
DROP TABLE IF EXISTS books;
CREATE TABLE authors (
author_id INTEGER,
author_name TEXT
);
CREATE TABLE books (
book_id INTEGER,
author_id INTEGER,
title TEXT
);
INSERT INTO authors VALUES
(1, 'Aditi Rao'),
(2, 'Kunal Shah'),
(3, 'Meera Das'),
(4, 'Ravi Sen');
INSERT INTO books VALUES
(601, 1, 'Learning SQL'),
(602, 3, 'Database Basics');
SELECT author_id, author_name
FROM authors AS a
WHERE EXISTS (
SELECT 1
FROM books AS b
WHERE b.author_id = a.author_id
)
ORDER BY author_id;EXISTS with a Subquery and Date Values
EXISTS can also be used when the related records must satisfy a date
condition. SQLite stores dates commonly as text in ISO format such as
YYYY-MM-DD.
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS visits;
CREATE TABLE customers (
customer_id INTEGER,
customer_name TEXT
);
CREATE TABLE visits (
visit_id INTEGER,
customer_id INTEGER,
visit_date TEXT
);
INSERT INTO customers VALUES
(1, 'Sana'),
(2, 'Ravi'),
(3, 'Tina'),
(4, 'Mohan');
INSERT INTO visits VALUES
(701, 1, '2026-07-10'),
(702, 2, '2026-06-15'),
(703, 3, '2026-08-05');
SELECT customer_id, customer_name
FROM customers AS c
WHERE EXISTS (
SELECT 1
FROM visits AS v
WHERE v.customer_id = c.customer_id
AND v.visit_date >= '2026-08-01'
)
ORDER BY customer_id;EXISTS with UPDATE
EXISTS can also be used inside an UPDATE statement to modify rows only when related records are found.
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS bonuses;
CREATE TABLE employees (
employee_id INTEGER,
employee_name TEXT,
status TEXT
);
CREATE TABLE bonuses (
bonus_id INTEGER,
employee_id INTEGER,
amount INTEGER
);
INSERT INTO employees VALUES
(1, 'Aarav', 'Regular'),
(2, 'Meera', 'Regular'),
(3, 'Kabir', 'Regular');
INSERT INTO bonuses VALUES
(801, 1, 5000),
(802, 3, 3000);
UPDATE employees
SET status = 'Bonus Eligible'
WHERE EXISTS (
SELECT 1
FROM bonuses AS b
WHERE b.employee_id = employees.employee_id
);
SELECT employee_id, employee_name, status
FROM employees
ORDER BY employee_id;EXISTS with DELETE
EXISTS can also be used with DELETE to remove rows when a related record exists in another table.
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS inactive_accounts;
CREATE TABLE customers (
customer_id INTEGER,
customer_name TEXT
);
CREATE TABLE inactive_accounts (
customer_id INTEGER
);
INSERT INTO customers VALUES
(1, 'Nikhil'),
(2, 'Pooja'),
(3, 'Tanya'),
(4, 'Dev');
INSERT INTO inactive_accounts VALUES
(2),
(4);
DELETE FROM customers
WHERE EXISTS (
SELECT 1
FROM inactive_accounts AS i
WHERE i.customer_id = customers.customer_id
);
SELECT customer_id, customer_name
FROM customers
ORDER BY customer_id;EXISTS vs IN
| Feature | EXISTS | IN |
|---|---|---|
| Main purpose | Checks whether rows exist | Checks whether a value belongs to a set |
| Commonly uses | Correlated subqueries | Lists or subqueries |
| Example | EXISTS (SELECT 1 …) | id IN (SELECT id …) |
EXISTS vs NOT EXISTS
| Operator | Result | Common Use |
|---|---|---|
| EXISTS | True when the subquery returns at least one row | Find records with related data |
| NOT EXISTS | True when the subquery returns no rows | Find records without related data |
Advantages of EXISTS
- Checks whether related records exist.
- Works well with correlated subqueries.
- Can be combined with multiple conditions.
- Supports both EXISTS and NOT EXISTS logic.
- Can be used with SELECT, UPDATE, and DELETE statements.
Best Practices
- Use EXISTS when you only need to know whether a related row exists.
- Use NOT EXISTS when you need records without a matching row.
- Use table aliases to make correlated subqueries easier to understand.
- Use
SELECT 1inside EXISTS when the actual selected value is not needed. - For repeatable SQLite examples, use
DROP TABLE IF EXISTSbefore creating tables.
The EXISTS operator is used to determine
whether a subquery returns at least one row. Together with
NOT EXISTS, it provides a powerful way to find records
that do or do not have related data.
🧪 Test Your SQL Code
Edit the SQL code on the left and click “Run Code” to see the result on the right.
Click “Run Code” to see the result here.