Search code examples
sqlselectbetweensql-in

SQL query for selecting records where any of a given list of integers is between columnA and columnB

How can I get records from my table where any of a list of integers is in the range defined by columnA and columnB integer values?

I know about the IN operator when comparing against a column value instead of a range defined by a pair of columns.

For example: select * from mytable where mytable.colA in (1,3,5,6); would get all records where colA is either 1,3,5 or 6.

Is there anything like that for ranges? Or should I do like:

select * from mytable where 1 between mytable.colA and mytable.colb OR 3 between mytable.colA and mytable.colb OR 5 between mytable.colA and mytable.colb OR 6 between mytable.colA and mytable.colb;

Solution

  • Maybe this way:

    select distinct mytable.*
from mytable
join (select 1 nr union all select 3 union all select 5 union all select 6) n
    on n.nr between mytable.colA and mytable.colb

    Update:

    Just tested on MariaDB (10.0.19) and a 1M-row indexed table.. Your original query is ways faster.