57

I have a table that has columns like this for example:

id,col1,col2,col3,col4

Now, I want to check if ANY of col1, col2, col3, col4 have the passed in value.

The long way to do it would be..

SELECT * FROM table WHERE (col1 = 123 OR col2 = 123 OR col3 = 123 OR col4 = 123);

I guess it's the opposite version of IN.

Is there an easier way to do what I want?

Brett
  • 19,449
  • 54
  • 157
  • 290

3 Answers3

141

You can use the IN predicate, like so:

SELECT * FROM table WHERE 123 IN(col1, col2, col3, col4);

SQL Fiddle Demo

it's the opposite version of IN.

No it is not, It is the same as using the ORs the way you did in your question.

To clarify this:

The predicate IN or set membership is defined as1:

enter image description here

Where the Value Expression can be either 2:

enter image description here

So it is fine to do it this way, using the value expression 123, which is a literal.

1, 2: Images from: SQL Queries for Mere Mortals(R): A Hands-On Guide to Data Manipulation in SQL

Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
  • How to pass more than one value as values to search for? for example "123" and "456". – Alagesan Palani Sep 08 '16 at 06:10
  • 3
    @AlagesanPalani - The same way: `SELECT * FROM YourTable WHERE columnName IN (123, 456');` – Mahmoud Gamal Sep 08 '16 at 07:47
  • 4
    no, i want something like select * from tablename where '123','456' in(col1,col2,col3) – Alagesan Palani Sep 08 '16 at 10:37
  • i use **set `uid1`=IF(`uid1` IS NULL, IF ('@myUniqueID' not in (uid2,uid3,uid4),'@myUniqueID', NULL ), `uid1`)** but not work – Iman Marashi Jun 06 '18 at 02:39
  • 1
    Excellent solution. It worked. I can't believe that I never tried this before. – manian Dec 28 '18 at 07:14
  • Is it also possible to find the "col1, col2, col3, col4" first with a subquery, and use that result? Something like this for a subquery: SELECT ALL COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'tableschema' AND TABLE_NAME = 'tablename' AND COLUMN_NAME LIKE '%_ID' – Jeroen Steen Jan 04 '23 at 12:08
2

You could do something like: (Note: Assuming the columns are numeric values. And, just incase the concatenated value creates the character sequence you are looking for, use a delimiter to distinguish the column values. Pipe (|) is the delimiter in this example.)

SELECT [ID]
    ,[Col1]
    ,[Col2]
    ,[Col3]
    ,[Col4]
FROM [Table1]
WHERE '123' IN (
    CAST([Col1] AS VARCHAR) + '|'
    + CAST([Col2] AS VARCHAR) + '|'
    + CAST([Col3] AS VARCHAR) + '|'
    + CAST([Col4] AS VARCHAR) + '|'
)
Dionicio Gonzalez
  • 21
  • 2
1

I had a similar problem and solved it this way:

SELECT *
FROM table
WHERE col1 OR col2 IN(SELECT xid FROM tablex WHERE somecol = 3)

Not sure if it is "the best way" but it works for me.

gre_gor
  • 6,669
  • 9
  • 47
  • 52
Milk Man
  • 1,256
  • 13
  • 21
  • This actually DOES work, even with in....Thank you! Example: SELECT * FROM table WHERE (owner_id, user_id) IN (1, 2, 3); – geilt Feb 16 '18 at 08:06
  • `col1 OR col2` doesn't do what you think it does. This query also returns any row where `col1` is truthy. – gre_gor May 31 '23 at 17:11
  • [It should only select rows "two", "three", "four", but it also selects "one"](https://www.db-fiddle.com/f/hxezgYkJT9Fc7xPFp6Z49r/2) – gre_gor May 31 '23 at 17:18