Match any value using SQLite IN() statement

Question

Part of a tool I’m building involves plugging user-supplied lists of values into prepared SQLite statements. For example, where a user supplies desired sizes and colors:

SELECT * from balloons WHERE size IN ("Medium", "Large") AND color IN ("Red", "Green", "Blue");

But, in some cases, some fields are optional and can be left blank by the user. When this happens, the tool should allow any value on the column in question. For example, where a user supplies desired colors but wishes to allow any size:

SELECT * from balloons WHERE size IN (~anything~) AND color IN ("Red", "Green", "Blue");

Ordinarily, I would simply remove the constraint on size. But, since the SQL statements are precomposed with placeholders, I am hoping to avoid parsing and rewriting them on the fly.

Is there any SQLite syntax that I can put inside the IN() statement which will match any value? Alternatively, is there a different way of forming these queries to allow either a specified list of values or any value at all? Or maybe I'm approaching the entire problem the wrong way; any thoughts are welcome.

Thanks in advance!

@CL. That works fine, it's only in cases where I want to effectively remove the constraint and allow anything that I'm having trouble. Thanks — Nathan
– Nathan, Commented Sep 27, 2017 at 14:25
But a different size implies a new precomposed statement. Why can you change the size, but not remove the IN? — CL.
– CL., Commented Sep 27, 2017 at 14:45
@CL. True. That works through a pretty simple find/replace that turns one ? into however many are needed (?, ?, ?). I was hoping to avoid any more involved parsing (like finding and removing the entire IN() statement), but I suspect that's what I'll have to do. — Nathan
– Nathan, Commented Sep 27, 2017 at 14:49

CL. · Accepted Answer · 2017-09-27 16:06:14Z

2

The IN operator always searches for the value in the list, and the list must contain at least one entry.

To construct an IN that has no effect, you could compare the column against itself:

... WHERE size IN (size) AND ...

But removing the IN altogether can make the query faster.

answered Sep 27, 2017 at 16:06

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Match any value using SQLite IN() statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related