SQL BETWEEN Operator

BETWEEN Operator

The SQL BETWEEN operator tests an expression against a range. The range consists of a beginning, followed by an AND keyword and an end expression. The operator returns TRUE when the search value present within the range otherwise returns FALSE. The results are NULL if any of the range values are NULL.

Syntax:

SELECT [column_name... | expression1 ]
FROM [table_name]
WHERE expression2 [NOT] BETWEEN value_from AND value_to;

Parameters:

DBMS Support: BETWEEN Operator

Example: SQL BETWEEN Operator

To know whether the search value 15 is present within the specified range from the DUAL table, the following SQL statement can be used:

SQL Code:

SELECT 'found' 
FROM dual 
WHERE 10 BETWEEN 5 AND 20;

Output:

'FOUND'
--------
found

Relational Algebra Expression:

Relational Algebra Tree:

Pictorial Presentation : SQL BETWEEN operator

Here we look for all agents in the agents table of inventory database whose commission should be within .12 to .14.

Sample table: agents

SQL Code:

SELECT * FROM agents 
WHERE commission BETWEEN .12 AND .14;  

Output:

AGENT_CODE AGENT_NAME           WORKING_AREA         COMMISSION PHONE_NO        COUNTRY
---------- -------------------- -------------------- ---------- --------------- ----------
A003       Alex                 London                      .13 075-12458969
A001       Subbarao             Bangalore                   .14 077-12346674
A008       Alford               New York                    .12 044-25874365
A010       Santakumar           Chennai                     .14 007-22388644
A012       Lucida               San Jose                    .12 044-52981425
A005       Anderson             Brisban                     .13 045-21447739

Pictorial Presentation: SQL BETWEEN operator

Example: SQL Between operator with IN

Sample table: customer

To get data of all columns from the 'customer' table with following conditions -

1. 'agent_code' must be within 'A003' and 'A008',

2. but 'agent_code' 'A004', 'A007' and 'A005' should not appear,

the following SQL statement can be used :

SQL Code:

SELECT agent_code, cust_code, cust_name, cust_city 
FROM customer
WHERE(agent_code BETWEEN 'A003' AND 'A008')
AND NOT agent_code IN('A004','A007','A005');

Output:

AGENT_CODE CUST_CODE  CUST_NAME                                CUST_CITY
---------- ---------- ---------------------------------------- ------------
A003       C00013     Holmes                                   London
A008       C00001     Micheal                                  New York
A008       C00020     Albert                                   New York
A006       C00024     Cook                                     London
A003       C00015     Stuart                                   London
A008       C00002     Bolt                                     New York
A006       C00023     Karl                                     London

Example: SQL Between operator with text value

Sample table: customer

To get data of 'cust_code', 'cust_name', 'cust_city', 'working_area' columns from the 'customer' table with following condition -

1.'cust_name' must begin with any of the letters between 'A' and 'I',

the following SQL statement can be used :

SQL Code:

SELECT cust_code, cust_name, cust_city, working_area
FROM customer
WHERE cust_name BETWEEN 'A' AND 'I';

Output:

CUST_CODE  CUST_NAME             CUST_CITY                           WORKING_AREA
---------- --------------------- ----------------------------------- --------------
C00013     Holmes                London                              London
C00020     Albert                New York                            New York
C00024     Cook                  London                              London
C00002     Bolt                  New York                            New York
C00018     Fleming               Brisban                             Brisban
C00022     Avinash               Mumbai                              Mumbai
C00010     Charles               Hampshair                           Hampshair

Relational Algebra Expression:

Relational Algebra Tree:

Example: SQL Between operator with boolean NOT

Sample table :customer

To get data of 'cust_code', 'cust_name', 'cust_city', 'working_area' columns from the 'customer' table with following condition -

1. 'cust_name' must not begin with any of the letters  between 'K' and 'Y' ,

the following SQL statement can be used :

SELECT cust_code, cust_name, cust_city, working_area
FROM customer
WHERE cust_name NOT BETWEEN 'K' AND 'Y';

Output

CUST_CODE  CUST_NAME                      CUST_CITY                           WORKING_AREA
---------- ------------------------------ ----------------------------------- -------------
C00013     Holmes                         London                              London
C00020     Albert                         New York                            New York
C00024     Cook                           London                              London
C00002     Bolt                           New York                            New York
C00018     Fleming                        Brisban                             Brisban
C00021     Jacks                          Brisban                             Brisban
C00019     Yearannaidu                    Chennai                             Chennai
C00022     Avinash                        Mumbai                              Mumbai
C00010     Charles                        Hampshair                           Hampshair

Relational Algebra Expression:

Relational Algebra Tree:

Example:SQL Between operator with NOT on date value

Sample table: orders

To get data of 'ord_num',  'ord_amount',  'advance_amount',  'ord_date', 'cust_code' and  'agent_code' from the 'orders' table with following condition -

1.'ord_date' must be a date before '15-Feb-08' or after '30-Jul-08',

the following SQL statement can be used :

SQL Code:

SELECT ord_num,ord_amount,ord_date, cust_code,agent_code 
FROM orders
WHERE ord_date NOT BETWEEN '15-Feb-08' AND '30-Jul-08';

Output:

   ORD_NUM ORD_AMOUNT ORD_DATE  CUST_CODE  AGENT_CODE
---------- ---------- --------- ---------- ----------
    200114       3500 15-AUG-08 C00002     A008
    200122       2500 16-SEP-08 C00003     A004
    200119       4000 16-SEP-08 C00007     A010
    200121       1500 23-SEP-08 C00008     A004
    200134       4200 25-SEP-08 C00004     A005
    200125       2000 10-OCT-08 C00018     A005
    200117        800 20-OCT-08 C00014     A001
    200123        500 16-SEP-08 C00022     A002
    200135       2000 16-SEP-08 C00007     A010
    200131        900 26-AUG-08 C00012     A012
    200100       1000 08-JAN-08 C00015     A003
    200107       4500 30-AUG-08 C00007     A010

Relational Algebra Expression:

Relational Algebra Tree:

See our Model Database

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.