Do SQL parsers just interpret IN as a series of OR conditions?

Question

When writing an SQL query such as

SELECT ID, NAME FROM USER_TABLE WHERE ID IN (1, 2, 10, 14, 15, ..., n)

does the parser just rephrase that into this?

SELECT ID, NAME FROM USER_TABLE WHERE ID = 1 
                                      OR ID =  2 
                                      OR ID =  10
                                      OR ID =  14
                                      OR ID =  15 
                                      ...
                                      OR ID =  n

Or does it do something else in the background for efficiency? While a nightmare to write out by hand and I would never advocate doing so, is there any theoretical performance benefit or hit to using IN rather than a series of OR conditions like that?

score 3 · Answer 1 · answered Dec 25 '18 at 14:57

That depends on the database. Logically, the IN is interpreted as a sequence of ORs, but that does not mean that is the underlying implementation.

For instance, MySQL will order a list of constants and use a binary search to find a match. That is quite different from a series of ORs.

Salman A · Accepted Answer · 2018-12-29T08:40:23.827

According to the specs (SQL92 for example), x IN (a, b, c) is described as:

8.4  <in predicate>

[...]

4) The expression
     RVC IN IPV
   is equivalent to
     RVC = ANY IPV

And:

8.7  <quantified comparison predicate>

[...]

<quantified comparison predicate> ::=
     <row value constructor> <comp op> <quantifier> <table subquery>

<quantifier> ::= <all> | <some>

<all> ::= ALL

<some> ::= SOME | ANY

[...]

   c) If the implied <comparison predicate> is true for at least
     one row RT in T, then "R <comp op> <some> T" is true.

The last line seems to suggest that x IN (a, b, c) is supposed to provide identical result as x = a OR x = b OR x = c. However the specs do not dictate how the RDBMs should implement the behavior, it could vary across RDBMs.

This following posts contain some interesting observations:

IN vs OR in the SQL WHERE Clause (MySQL and other)
SQL IN() versus OR (SQL Server)

SQL Server seems to generate same execution plan for x IN (...) and x = ... OR x = ... where as MySQL handles them differently.

For SQL Server have a look at https://blog.sqlauthority.com/2018/06/13/sql-server-performance-comparison-in-vs-or/ and for others have a look at https://stackoverflow.com/questions/3074713/in-vs-or-in-the-sql-where-clause. SQL Server simply translates IN to multiple ORs, MySQL handles them differently. — Salman A, Dec 26 '18 at 21:13

Do SQL parsers just interpret IN as a series of OR conditions?

2 Answers2