EXISTS returns TRUE if a subquery returns any rows. Efficient for checking existence.

Basic EXISTS

SELECT * FROM customers c

WHERE EXISTS (

SELECT 1 FROM orders o WHERE o.customer_id = c.id

);

NOT EXISTS

SELECT * FROM customers c

WHERE NOT EXISTS (

SELECT 1 FROM orders o WHERE o.customer_id = c.id

);

EXISTS vs IN

-- Using EXISTS (often faster for large datasets)

SELECT * FROM products p

WHERE EXISTS (SELECT 1 FROM order_items oi WHERE oi.product_id = p.id);

-- Using IN

SELECT * FROM products

WHERE id IN (SELECT product_id FROM order_items);

EXISTS with Conditions

SELECT * FROM customers c

WHERE EXISTS (

SELECT 1 FROM orders o

WHERE o.customer_id = c.id

AND o.total > 1000

);

Conditional INSERT

INSERT INTO notifications (user_id, message)

SELECT id, 'Welcome!' FROM users u

WHERE NOT EXISTS (

SELECT 1 FROM notifications n WHERE n.user_id = u.id

);

Generate EXISTS Queries

AI2sql uses EXISTS for optimal performance.