What difference it makes if I use OR statements instead of IN in SQL

What difference it makes if I use, winner IN ('Subject1','Subject2'); & winner='Subject1' OR winner='Subject2';

winner IN ('Subject1','Subject2');

winner='Subject1' OR winner='Subject2';

Queries for the table 17 in the below link:

https://www.w3resource.com/sql-exercises/sql-retrieve-from-table.php#SQLEDITOR

winner IN (SELECT columnX FROM tableY WHERE somePredicate)

4 Answers
4

For lists with two elements it doesn't make a difference.

However, MySQL optimizes IN when the list consists of constant expressions. It basically sorts them and does a binary search through the list. This can be a considerable savings with longer lists. As the documentation explains:

IN

If all values are constants, they are evaluated according to the type
of expr and sorted. The search for the item then is done using a
binary search. This means IN is very quick if the IN value list
consists entirely of constants.

In general, IN is safer and does a better job of capturing the column you want. It is very easy to take conditions like this:

IN

where winner = 'Subject1' OR winner = 'Subject2'


and add another condition:

where winner = 'Subject1' or winner = 'Subject2' and
foo = 'bar'


and this logic is probably not longer what you really want -- because it really means:

where winner = 'Subject1' or
(winner = 'Subject2' and foo = 'bar')


This doesn't happen with IN:

IN

where winner in ('Subject1', 'Subject2') and
foo = 'bar'


and the logic is not longer what you really want.

If there's an index on the column in question, IN vastly out-performs OR. Experience has shown me that the db consistently doesn't use the index when there's an OR on the column.

IN

OR

OR

If there's no index on the column in question, IN out-performs OR if the list is longer than about 5 (it's faster to do a few serial comparisons than traverse a small BTree of values, which is what the DB turns the list into for execution).

IN

OR

IN is also preferred for readability and avoiding SQL's operator precedence trap if brackets are omitted, ie x = a or x = b and c = d is parsed as x = a or (x = b and c = d) instead of the (perhaps) expected (x = a or x = b) and c = d.

IN

x = a or x = b and c = d

x = a or (x = b and c = d)

(x = a or x = b) and c = d

It isn't different result between OR statement and IN,and I think SQL syntax executor will help in and or find the same best solution, But I will use IN rather than OR statement, because IN easier reading than OR statement.

OR

IN

IN

OR

IN

OR

Careful when using NOT:

select col1 from
(
select 1 as col1
union all
select 2 as col1
union all
select 3 as col1
union all
select 4 as col1
)x
where x.col1 NOT IN (2,3,4) ;
----------
col1
1


However

select col1 from
(
select 1 as col1
union all
select 2 as col1
union all
select 3 as col1
union all
select 4 as col1
)x
where x.col1 != 2 OR x.col1 != 3 OR x.col1 != 4 ;

---
col1
1
2
3
4


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.