Skip to main content

EXISTS Operator

EXISTS checks whether a subquery returns at least one row.

It is perfect for "has related rows" and "missing related rows" checks.

Core Concepts​

flowchart LR
  A[Outer row] --> B[Run subquery]
  B --> C{Any rows returned?}
  C -->|Yes| D[EXISTS = true]
  C -->|No| E[EXISTS = false]
PatternMeaning
WHERE EXISTS (...)Keep rows with at least one match
WHERE NOT EXISTS (...)Keep rows with no matches
-- Customers who placed at least one order.
SELECT c.customer_id, c.name
FROM customers_ex AS c
WHERE EXISTS (
  SELECT 1
  FROM orders_ex AS o
  WHERE o.customer_id = c.customer_id
)
ORDER BY c.customer_id;
customer_idname
1Ada
2Alan

Code Examples​

-- Setup parent-child tables.
CREATE TABLE customers_ex (
  customer_id INTEGER PRIMARY KEY,
  name        TEXT NOT NULL
);

CREATE TABLE orders_ex (
  order_id     INTEGER PRIMARY KEY,
  customer_id  INTEGER NOT NULL,
  total        REAL NOT NULL
);

INSERT INTO customers_ex (customer_id, name)
VALUES (1, 'Ada'), (2, 'Alan'), (3, 'Grace');

INSERT INTO orders_ex (order_id, customer_id, total)
VALUES (9001, 1, 60.0), (9002, 1, 45.0), (9003, 2, 75.0);
Expected output
Tables created and sample rows inserted.
-- Exists check with value filter.
SELECT c.customer_id, c.name
FROM customers_ex AS c
WHERE EXISTS (
  SELECT 1
  FROM orders_ex AS o
  WHERE o.customer_id = c.customer_id
    AND o.total >= 70
)
ORDER BY c.customer_id;
customer_idname
2Alan

SQLite-Specific Nuances​

SQLite Nuance

EXISTS in SQLite only checks row existence; selected columns in the subquery are ignored.

SELECT 1 is a common, clear convention.

Common Pitfalls / Best Practices​

Pitfall

Using COUNT(*) > 0 everywhere instead of EXISTS, making intent less explicit.

Best Practice

Use EXISTS/NOT EXISTS for relationship presence checks and keep subqueries tightly scoped.

Quick Challenge​

Return customers who have at least one order below 50.

View Solution
SELECT c.customer_id, c.name
FROM customers_ex AS c
WHERE EXISTS (
  SELECT 1
  FROM orders_ex AS o
  WHERE o.customer_id = c.customer_id
    AND o.total < 50
)
ORDER BY c.customer_id;
customer_idname
1Ada