SQL select query with large set of id's in where clause

Question

I have below SQL query which i need to pass around 50K id's in the WHERE clause,

select columnA 
from TableA 
WHERE COLUMNB ...

I tried to use IN(...) syntax but it's accepting maximum 5K entries.Can you tell the best approach to pass and execute the sql with this large dataset in the WHERE clause.

Thanks in Advance, Raghavan

Put those 50k rows in a temp table and join. – jarlh Apr 18 '17 at 09:03 — jarlh, Apr 18 '17 at 09:03

score 0 · Answer 1 · answered Apr 18 '17 at 09:04

You could load these IDs into an actual table, temporary or otherwise, and then join to it:

SELECT a.columnA
FROM TableA a
INNER JOIN TableB b
    ON a.COLUMNB = b.some_col

Actually, with 50K data points, this data almost certainly should be managed in a bona-fide table if not being done already. Having that much data on the loose is prone to error.

SQL select query with large set of id's in where clause

1 Answers1