0

What is better option? Using IN operator or EXISTS operator? In term of performance and server load. Is there any supporting factors for IN and/or EXISTS operator in database (like index, constraint or something)?

Here is sample queries using IN and EXISTS

SELECT * FROM Customers WHERE Customer_ID IN (SELECT Cust_ID FROM Sales);

AND

SELECT Customer_ID FROM Customers WHERE EXISTS (SELECT Cust_ID FROM Sales);

If the two queries were different, what is better way to count or list Customer? Or if query is more complex in like below. 

SELECT sub_id FROM subscription 
WHERE start_date = CURDATE()
AND end_date > CURDATE()
AND sub_id NOT IN (SELECT DISTINCT sub_id FROM subscription 
WHERE start_date < CURDATE());

Is it possible to replace NOT IN operator with NOT EXISTS? In this case, rewriting above query with NOT EXISTS is better or something?

CPMM
  • 76
  • 7

2 Answers2

4

The two queries do different things. You probably intend a correlated subquery for EXISTS:

SELECT Customer_ID c
FROM Customers c
WHERE EXISTS (SELECT 1 FROM Sales s WHERE s.Cust_ID = c.Customer_Id);

Both methods are fine for expressing your logic. I tend to prefer EXISTS for two reasons:

  • NOT EXISTS is generally a better choice than NOT IN because of the way it handles NULL. This does not apply to EXISTS/IN, but it spills over.
  • EXISTS is generally no worse than IN from a performance perspective.
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

You could use them for the same, but exists keyword is generaly used when you are checking if conditional statements.

In keyword, is generaly used with a list comparation.

Also, as jarlh says, you can replace in using join.

Víctor López
  • 819
  • 1
  • 7
  • 19