SELECT WHERE ... hundreds of conditions

Question

Is there an elegant way to do this:

SELECT Cols from MyTable WHERE zip = 90210 OR zip = 23310 OR zip = 74245 OR zip = 77427 OR zip = 18817 OR zip = 94566 OR zip = 34533 OR zip = 96322 OR zip = 34566 OR zip = 52214 OR zip = 73455 OR zip = 52675 OR zip = 54724 OR zip = 98566 OR zip = 92344 OR zip = 90432 OR zip = 91532 OR ...

(zip codes in this post are ficticious and bear no resemblance to actual zip codes living or dead)

Answer 1

Yes:

Select cols from MyTable where zip in (90210, 23310, ... etc.)

Ben

Answer 2

Depends on the definition of "elegant" :)

However, with that many ZIP codes, I think you might want to manage them in the DB too.

How do you decide which ZIP codes to match against?

So you could put the ZIP codes in a table of their own and do

SELECT cols FROM MyTable, ZipTable WHERE MyTable.zip = ZipTable.zip

Answer 3

Or a combination of both, a nested query:

SELECT cols FROM MyTable WHERE zip IN 
    (SELECT zip FROM ZipTable WHERE condition=true)

Answer 4

What Ben said. You should also be able straight up pass an array in place of that (90210, 23310, ... etc.) there.

Answer 5

I'd use something like:

start transaction; (don't remember the idiom for it)

create temporary table if not exists ZIPS(ZIP integer) storage=memory;

insert into ZIPS (ZIP) VALUES(the values you want to insert)

select COLS from MYTABLE M, ZIPS Z where Z.ZIP = M.ZIP

drop table ZIPS (or truncate it or do whatever you wish)

either commit or rollback

In every db API you should have some variation of executemany function, which can call insert into TABLE(COLUMNS) VALUES with multiple value rows and be faster than repeating single insert. And you can wrap such call sequence in some function, for easy reuse, as a temporary table with one INT column is often handy :-)

This way you can avoid problem with maximum SQL query length problem (e.in g. MySQL) and your query is effective, clean and easy to maintain or extend further.