How can I select a range of items, after some values using SQL IN operator

1

For example: I want to search, 1,2,5,7,11 and then the rest until 100, in a DQL SELECT. Ex.:

SELECT column_name(s) FROM table_name WHERE column_name IN (1,2,5,7,11 ... value100);

In case my doubt is regarding the use of the IN operator, it would not be BETWEEN

    
asked by anonymous 24.05.2018 / 20:15

2 answers

4

Assuming you have a table like:

CREATE TABLE tb_foobar
(
    id INTEGER PRIMARY KEY,
    value INTEGER
);

Containing the following data:

INSERT INTO tb_foobar ( id, value ) VALUES (  1,   1 );
INSERT INTO tb_foobar ( id, value ) VALUES (  2,   2 );
INSERT INTO tb_foobar ( id, value ) VALUES (  3,   3 );
INSERT INTO tb_foobar ( id, value ) VALUES (  4,   4 );
INSERT INTO tb_foobar ( id, value ) VALUES (  5,   5 );
INSERT INTO tb_foobar ( id, value ) VALUES (  6,   6 );
INSERT INTO tb_foobar ( id, value ) VALUES (  7,   7 );
INSERT INTO tb_foobar ( id, value ) VALUES (  8,   8 );
INSERT INTO tb_foobar ( id, value ) VALUES (  9,   9 );
INSERT INTO tb_foobar ( id, value ) VALUES ( 10,  10 );
INSERT INTO tb_foobar ( id, value ) VALUES ( 11,  11 );
INSERT INTO tb_foobar ( id, value ) VALUES ( 12,  50 );
INSERT INTO tb_foobar ( id, value ) VALUES ( 13,  60 );
INSERT INTO tb_foobar ( id, value ) VALUES ( 14,  80 );
INSERT INTO tb_foobar ( id, value ) VALUES ( 15, 100 );
INSERT INTO tb_foobar ( id, value ) VALUES ( 16, 120 );
INSERT INTO tb_foobar ( id, value ) VALUES ( 17, 200 );

You can do something like:

SELECT
    id,
    value
FROM
    tb_foobar
WHERE
    value IN ( 1, 2, 5, 7 ) OR
    value BETWEEN 11 AND 100;

Output:

| id | value |
|----|-------|
|  1 |     1 |
|  2 |     2 |
|  5 |     5 |
|  7 |     7 |
| 11 |    11 |
| 12 |    50 |
| 13 |    60 |
| 14 |    80 |
| 15 |   100 |

SQLFiddle: link

    
24.05.2018 / 21:06
3

In this case you have to use BETWEEN :

SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value100;

See more about BETWEEN here .

    
24.05.2018 / 20:18