IN vs OR in the SQL WHERE Clause

0 votes
What is the difference between IN  vs OR SQL in the where clause?
Sep 24, 2018 in Database by Sahiti
• 6,370 points
3,962 views

1 answer to this question.

0 votes

I assume you want to know the performance difference between the following:

WHERE foo IN ('a', 'b', 'c')
WHERE foo = 'a' OR foo = 'b' OR foo = 'c'

According to the official manual if the values are constant IN sorts the list and then uses a binary search. 

I would imagine that OR evaluates them one by one in no particular order. So IN is faster in some circumstances.

The best way to know is to profile both on your database with your specific data to see which is faster.

I tried both on a MySQL with 1000000 rows. When the column is indexed there is no discernable difference in performance - both are nearly instant. When the column is not indexed I got these results:

SELECT COUNT(*) FROM t_inner WHERE val IN (1000, 2000, 3000, 4000, 5000, 6000, 7000, 8000, 9000);
1 row fetched in 0.0032 (1.2679 seconds)

SELECT COUNT(*) FROM t_inner WHERE val = 1000 OR val = 2000 OR val = 3000 OR val = 4000 OR val = 5000 OR val = 6000 OR val = 7000 OR val = 8000 OR val = 9000;
1 row fetched in 0.0026 (1.7385 seconds)

So, in this case, the method using OR is about 30% slower. Adding more terms makes the difference larger. Results may vary on other databases and on other data.

answered Sep 24, 2018 by DataKing99
• 8,250 points

Related Questions In Database

0 votes
1 answer

Ordering by the order of values in a SQL IN() clause

Use MySQL's FIELD() function: SELECT name, description, ... FROM ... WHERE id ...READ MORE

answered Feb 4, 2022 in Database by Neha
• 9,020 points
5,330 views
0 votes
1 answer

What is the difference between HAVING and WHERE in SQL?

HAVING: It is used to check after the aggregation ...READ MORE

answered Feb 17, 2022 in Database by Vaani
• 7,070 points
788 views
0 votes
0 answers

SQL use CASE statement in WHERE IN clause

Can you use a case statement in ...READ MORE

Aug 9, 2022 in Database by Kithuzzz
• 38,000 points
706 views
0 votes
0 answers

Execution sequence of Group By, Having and Where clause in SQL Server?

I'm just not sure how a SQL ...READ MORE

Aug 19, 2022 in Database by Kithuzzz
• 38,000 points
612 views
0 votes
0 answers

How to use the where clause in R programming?

I'm trying to implement a where clause ...READ MORE

Dec 24, 2018 in Data Analytics by Sophie may
• 10,620 points
4,234 views
0 votes
1 answer

What is the rule to use group by, having and where clause?

Hi samar, this is a very common mistake ...READ MORE

answered Jul 2, 2019 in Database by anonymous
1,325 views
0 votes
1 answer

SQL Switch/Case in 'where' clause

Without a case statement: SELECT column1, ...READ MORE

answered Feb 7, 2022 in Database by Vaani
• 7,070 points
1,444 views
0 votes
0 answers

What is the purpose of using WHERE 1=1 in SQL statements?

Possible Duplicates: Why would a sql query have ...READ MORE

Aug 28, 2022 in Database by Kithuzzz
• 38,000 points
2,282 views
0 votes
1 answer

Clause in SQL

SQL clause helps to limit the result ...READ MORE

answered Oct 8, 2018 in Database by DataKing99
• 8,250 points
710 views
0 votes
1 answer

What are the manipulation functions in SQL?

There are three case manipulation functions in ...READ MORE

answered Oct 11, 2018 in Database by DataKing99
• 8,250 points
5,492 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP