CODEWITHSUNDEEP
mysqlsql
Saurabh Pal
Saurabh Pal

Reputation: 1

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';

Queries for the table 17 in the below link:

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

Upvotes: 0

Views: 147

Answers (3)

Victor Di Leo
Victor Di Leo

Reputation: 82

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

Upvotes: 0

Bohemian
Bohemian

Reputation: 425003

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.

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 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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269743

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:

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:

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:

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

Upvotes: 2

Related Questions