Cost difference between 'WHERE A=x' and 'WHERE A IN (x)'?

Question

If I want to find records where a given field is equal to some value, I use

SELECT * from TableName WHERE FieldName=value;

If I want to select records which match a list of values, I can use

SELECT * from TableName WHERE FieldName IN (value1, value2, ..., valueN);

~~to avoid having WHERE FieldName = value1 OR FieldName = value2 OR...~~ (edited since seemingly this misled about the purpose of the question)

Using .eqp on seems to suggest that when my list contains only one element, the two queries are equivalent.

Is this true?

If so, can I freely use WHERE FieldName IN (...) and parse values into a comma separated list (avoiding trailing commas, etc) without cost to the query?

It would seem this will greatly simplify coding my queries, but I am surprised that there isn't an additional complexity cost from the IN operator, regardless of the list being only one element.

Edit: To be clear, I won't use OR in any case - if I have one value, then previously I had been using the first form (FieldName=value). If I had several values, I used IN (val1, val2, ...). Based on comments, it would seem that I can remove the code I need to use to handle these, and instead search only for IN (value1).

Possible duplicate of [IN vs OR in the SQL WHERE Clause](http://stackoverflow.com/questions/3074713/in-vs-or-in-the-sql-where-clause) — Tim Biegeleisen, Jan 24 '17 at 05:09
The information given in the answers there is useful, but the question is different. — chrisb2244, Jan 24 '17 at 05:09
But my question doesn't want to know about costs of IN vs OR. I want to know about the query in the title - `IN (x)` vs `=x` where x is a single value. — chrisb2244, Jan 24 '17 at 05:11
For a single value, there should not be any difference AFAIK. — Tim Biegeleisen, Jan 24 '17 at 05:11
Thanks. Perhaps discussing `OR` in the question was misleading. Certainly it seems that SO thinks I wanted to know about `IN` vs `OR` - unless that is because of the close vote as duplicate. — chrisb2244, Jan 24 '17 at 05:14
If you're worried about `IN` having a performance penalty with a single item versus `OR`, then this does not appear to be the case. So you should be at an advantage using `IN` for single _or_ multiple items. — Tim Biegeleisen, Jan 24 '17 at 05:15
@muistooshort I mispoke, I must have been thinking of MariaDB or something else. Bad on me. — Tim Biegeleisen, Jan 24 '17 at 05:28

score 1 · Accepted Answer · answered Jan 24 '17 at 06:31

When SQLite estimates that the IN list is so short that its overhead is larger than that of an OR-connected series of equality comparisons, it converts the query into the latter form. Currently, this is done for IN lists of size 1 or 2.

As shown by EXPLAIN, the code for a single item is identical:

sqlite> explain select * from t where x = 1;
addr  opcode         p1    p2    p3    p4             p5  comment      
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     10    0                    00  Start at 10  
1     OpenRead       0     2     0     1              00  root=2 iDb=0; t
2     Rewind         0     8     0                    00               
3       Column         0     0     1                    00  r[1]=t.x     
4       Ne             2     7     1     (BINARY)       51  if r[1]!=r[2] goto 7
5       Copy           1     3     0                    00  r[3]=r[1]    
6       ResultRow      3     1     0                    00  output=r[3]  
7     Next           0     3     0                    01               
8     Close          0     0     0                    00               
9     Halt           0     0     0                    00               
10    Transaction    0     0     1     0              01  usesStmtJournal=0
11    TableLock      0     2     0     t              00  iDb=0 root=2 write=0
12    Integer        1     2     0                    00  r[2]=1       
13    Goto           0     1     0                    00               
sqlite> explain select * from t where x in (1);
addr  opcode         p1    p2    p3    p4             p5  comment      
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     10    0                    00  Start at 10  
1     OpenRead       0     2     0     1              00  root=2 iDb=0; t
2     Rewind         0     8     0                    00               
3       Column         0     0     1                    00  r[1]=t.x     
4       Ne             2     7     1     (BINARY)       51  if r[1]!=r[2] goto 7
5       Copy           1     3     0                    00  r[3]=r[1]    
6       ResultRow      3     1     0                    00  output=r[3]  
7     Next           0     3     0                    01               
8     Close          0     0     0                    00               
9     Halt           0     0     0                    00               
10    Transaction    0     0     1     0              01  usesStmtJournal=0
11    TableLock      0     2     0     t              00  iDb=0 root=2 write=0
12    Integer        1     2     0                    00  r[2]=1       
13    Goto           0     1     0                    00

(SQLite also does the opposite transformation when you have too many OR terms.)

Cost difference between 'WHERE A=x' and 'WHERE A IN (x)'?

1 Answers1