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 darklord
• 6,140 points

1 answer to this question.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.
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,100 points

Related Questions In Database

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,100 points
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 darklord
• 6,140 points
0 votes
1 answer

what is the syntax of OR statement in MySQL?

The OR operator displays those records which ...READ MORE

answered Nov 30, 2018 in Database by darklord
• 6,140 points
0 votes
2 answers


Well, it depends on what you are ...READ MORE

answered Aug 10, 2018 in Database by slayer
• 29,040 points
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
• 9,050 points
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,260 points
0 votes
1 answer

How to connect Java program to the MySQL database?

You can connect your Java code with ...READ MORE

answered May 11, 2018 in Java by parth295
• 4,590 points
0 votes
1 answer
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,100 points
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,100 points

© 2018 Brain4ce Education Solutions Pvt. Ltd. All rights Reserved.
"PMP®","PMI®", "PMI-ACP®" and "PMBOK®" are registered marks of the Project Management Institute, Inc. MongoDB®, Mongo and the leaf logo are the registered trademarks of MongoDB, Inc.