-- Using JOIN when EXISTS is sufficient

SELECT DISTINCT c.customer_name

FROM customers c

INNER JOIN orders o ON c.customer_id = o.customer_id

WHERE o.total_amount > 500;

-- Use EXISTS for filtering without data combination

SELECT c.customer_name

FROM customers c

WHERE EXISTS (

SELECT 1 FROM orders o

WHERE o.customer_id = c.customer_id

AND o.total_amount > 500

);

EXISTS stops at first match and avoids duplicate elimination. Use JOIN only when you need data from both tables.

JOIN with DISTINCT is less efficient than EXISTS for filtering

-- Using IN with potential NULL values

SELECT customer_name

FROM customers

WHERE customer_id IN (

SELECT customer_id FROM orders -- May contain NULL

WHERE total_amount > 1000

);

-- Use EXISTS to handle NULLs correctly

SELECT customer_name

FROM customers c

WHERE EXISTS (

SELECT 1 FROM orders o

WHERE o.customer_id = c.customer_id

AND o.total_amount > 1000

);

EXISTS handles NULL values correctly. IN may return no results if subquery contains NULL values.

IN with NULL values can give unexpected results

-- Using COUNT(*) > 0 instead of EXISTS

SELECT customer_name

FROM customers c

WHERE (

SELECT COUNT(*) FROM orders o

WHERE o.customer_id = c.customer_id

) > 0;

-- Use EXISTS for existence checks