How to identify a number is available in a range of numbers in 2 columns in SQL

Question

I have a table with 2 columns a and b. I want to write a query to search a number is available in the column or the number falls between those 2 columns. I tried using >=,<= also with between but it is not returning any result.

Could someone help me with the query?

• If the input parameter is 1, it should return true.
• If the input parameter is 3, it should return true.
• If the input parameter is 7, it should return true.
• If the input parameter is 15, it should return false.

Thanks

Table with values and expected output:

Answer 1

Say you have a couple of tables like the following:

CREATE TABLE number_ranges (
    A INT,
    B INT
);

INSERT INTO number_ranges (A, B) VALUES (1, 1), (2, 2), (3, 3), (5, 10), (20, 100);

CREATE TABLE search_inputs (
    `Search(Input)` INT
);

INSERT INTO search_inputs (`Search(Input)`) VALUES (1), (3), (7), (15);

You can use BETWEEN, CASE, and EXISTS to achieve your desired output:

SELECT 
    si.`Search(Input)`,
    CASE 
        WHEN EXISTS (
                 SELECT 1 
                 FROM number_ranges nr 
                 WHERE si.`Search(Input)` BETWEEN nr.A AND nr.B
             )
        THEN 'TRUE'
        ELSE 'FALSE'
    END AS Output
FROM search_inputs si;

Output:

Search(Input)	Output
1	TRUE
3	TRUE
7	TRUE
15	FALSE

Answer 2

SQL Server answer:

select max(case when yournumber between A and B then 'true' else 'false' end)
from tablename

ANSI/ISO SQL answer:

select max(yournumber between A and B) from tablename

If yournumber is equal to A or B, or between A and B, you get TRUE for that row in your table, otherwise FALSE.

Do MAX() to get TRUE if at least one row is TRUE. And if none is TRUE, you'll get FALSE.

(If A can be greater than B, do between symmetric.)