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,380 points
516 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,240 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 in Database by Neha
• 8,560 points
599 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 in Database by Vaani
• 7,020 points
62 views
0 votes
1 answer

List the ways in which Dynamic SQL can be executed

Following are the ways in which dynamic ...READ MORE

answered Oct 26, 2018 in Database by DataKing99
• 8,240 points
524 views
0 votes
1 answer

What are the different authentication modes in SQL Server? How can it be changed?

Windows mode and Mixed Mode – SQL ...READ MORE

answered Oct 29, 2018 in Database by Sahiti
• 6,380 points
610 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,590 points
3,059 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
624 views
0 votes
1 answer

SQL Switch/Case in 'where' clause

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

answered Feb 7 in Database by Vaani
• 7,020 points
81 views
0 votes
1 answer

Which query to use for better performance, join in SQL or using Dataset API?

DataFrames and SparkSQL performed almost about the ...READ MORE

answered Apr 19, 2018 in Apache Spark by kurt_cobain
• 9,390 points
965 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,240 points
205 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,240 points
3,415 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP