What is the difference between EXISTS and IN?

EXISTS checks if subquery returns any rows and stops at first match. IN checks if value exists in subquery result set. EXISTS handles NULL values correctly, IN may not. EXISTS is often faster for large datasets because it short-circuits. Use EXISTS for correlated subqueries, IN for simple value lists.

When should you use EXISTS instead of JOIN?

Use EXISTS when you only need to filter rows based on existence of related data, not combine data from both tables. EXISTS is more efficient for filtering because it stops at first match and avoids duplicate elimination. Use JOIN when you need columns from both tables.

How does NOT EXISTS differ from LEFT JOIN + IS NULL?

Both find non-matching rows but work differently. NOT EXISTS uses correlated subquery and stops when match found. LEFT JOIN + IS NULL creates full result set then filters. NOT EXISTS often performs better and handles complex conditions more naturally. Both are valid anti-join patterns.

Find customers who have placed orders in the Electronics category.

SELECT c.customer_name, c.email

FROM customers c

WHERE EXISTS (

SELECT 1 FROM orders o

INNER JOIN order_items oi ON o.order_id = oi.order_id

INNER JOIN products p ON oi.product_id = p.product_id

WHERE o.customer_id = c.customer_id

AND p.category = 'Electronics'

);

EXISTS with complex subquery joining multiple tables. Efficiently filters customers based on product category purchases.

Write a query to find products that are in active categories but have no current inventory.

SELECT p.product_name, p.category

FROM products p

WHERE EXISTS (

SELECT 1 FROM categories cat

WHERE cat.category_name = p.category

AND cat.status = 'active'

)

AND NOT EXISTS (

SELECT 1 FROM inventory i

WHERE i.product_id = p.product_id

AND i.quantity > 0

);

Combines EXISTS and NOT EXISTS. First checks for active category, then ensures no current inventory.

Find employees who have completed all required training modules.

SELECT e.employee_name, e.department

FROM employees e

WHERE NOT EXISTS (

SELECT 1 FROM training_modules tm

WHERE tm.required = 1

AND tm.department = e.department

AND NOT EXISTS (

SELECT 1 FROM employee_training et