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
(edited since seemingly this misled about the purpose of the question)WHERE FieldName = value1 OR FieldName = value2 OR...
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).
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.)