Search code examples
mysqlsqlselectquery-optimization

Checking multiple columns for one value with greater than or equal (>=)

Let's say i'm having a table like this:

id,col1,col2,col3,col4

I wish to check if any of col1,col2,col3,col4 are greater than or equal 10

The idea was smth like

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

Is there any more optimized way?

I thought that I could use IN, but as don't have any clue how to use >= in it.

Solution

  • Assuming none of the values are NULL, you can use greatest():

    SELECT *
FROM table
WHERE GREATEST(col1, col2, col3, col5) >= 10;

    This is no more efficient, but it is shorter.