Thursday, October 01, 2009

MySQL IN clause gem

Well today was a good day as I found a GEM for the MySQL IN clause. Normally I would use the IN clause to search for multiple values in a field like this:


SELECT *
FROM tbl_name
WHERE field_name
IN ('value_1', 'value_2', 'value_3', 'value_x')


But today discovered that you can reverse the fields and values. So instead of using the OR clause, you could use something like this:


SELECT *
FROM tbl_name
WHERE value_x
IN ('field_name_1', 'field_name_2', 'field_name_3', 'field_name_x')


This makes it super simple to search for one value across multiple fields.

6 comments:

Chitwan Malhotra said...

Good to know this..
Thanks phill for the useful post.

though it seems the field_name should not be quoted.

Poornima Helene Ravi said...

That was very useful indeed! but can you suggest a solution for this:
If your query value is: 'MNOPQRSTUVLMNOPSDT'
Then, the select should fetch records like, say, NOPQ or STUVLMN or OPQRSTUV or PSDT or etc from the database.
Is it possible to get it done using WHERE IN clause?

Phill said...

That was very useful indeed! but can you suggest a solution for this:
If your query value is: 'MNOPQRSTUVLMNOPSDT'
Then, the select should fetch records like, say, NOPQ or STUVLMN or OPQRSTUV or PSDT or etc from the database.
Is it possible to get it done using WHERE IN clause?

Not sure what you mean, but maybe try %LIKE%

nancy sept said...

What if something is like this
(value1,value2) in (field1,feild2)

nancy sept said...

But if the condition is like this
(val1,val2) in (field1,field2)

Phill Pafford said...

You could use the OR condition like this:

SELECT *
FROM tbl_name
WHERE value_x
IN ('field_name_1', 'field_name_2', 'field_name_3', 'field_name_x')
OR value_y
IN ('field_name_1', 'field_name_2', 'field_name_3', 'field_name_x')