0

I have a MySQL query that searches for rows with a column value belonging to a very large set:

SELECT * FROM table
WHERE col IN (someVal1, someVal2, someVal2, ...);

and the set with someVal1, someVal2, someVal3, ... contains thousands of entries. This makes the query pretty difficult to read because that line takes up so much space. Is there a better way to check if a column value is in this set without writing the full set directly into the query?

kjh
  • 3,407
  • 8
  • 42
  • 79

2 Answers2

1

I'd suggest creating a separate table with the values in it and then you can join to that table.

This will make your code easier to maintain as you can add and remove entries to that table easily without changing the code.

SELECT t.* 
FROM table AS t
JOIN somevals AS v 
ON t.col = v.val;

Where somevals is the table containing your thousands of values

You may have to tweak the syntax to get that to run on MySQL

You should definitely have a primary key on the matching column in your list table (somevals.val) and quite possibly add an index on the one that you're joining to it (table.col)

Tom Page
  • 1,211
  • 1
  • 7
  • 8
0

If the set is in the database then you can use a select:

SELECT * FROM table WHERE col IN (SELECT col_name FROM table_name WHERE x=z);
Bruno Domingues
  • 43
  • 1
  • 7