w3resource logo


>SQL Between operator

SQL BETWEEN Operator

<<PreviousNext>>

Seondary Nav

Description

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 1 when the search value present within the range otherwise returns 0.

Syntax

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

Parameters

Name Description
column_name Name of the column of the table.
expression Expression made up of a single constant, variable, scalar function, or column name and can also be the pieces of a SQL query that compare values against other values or perform arithmetic calculations.
table_name Name of the table.
value_from,value_to Starting value and ending value.

Example

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

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

Output

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

Sample table : agents

To get data of all columns from the 'agents' table with following condition -

1. percentage of commission for the 'agents' should be within .12 to .14,

the following sql statement can be used :

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

SQL Between operator with IN

In the following we are discussing the usage of SQL BETWEEN operator using IN operator in the select statement.

Example

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 :

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

SQL Between operator with text value

In the following we are discussing the usage of SQL BETWEEN operator using text value in the select statement.

Example

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 be begin with any of the letter between 'A' and 'I',

the following sql statement can be used :

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

SQL Between operator with boolean NOT

In the following we are discussing the usage of SQL BETWEEN operator using boolean 'NOT' operator in a select statement.

Example

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 letter  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

SQL Between operator with NOT on date value

In the following we are discussing the usage of SQL BETWEEN operator using boolean 'NOT' operator on date value in a select statement.

Example

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 :

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
    200115       2000 08-FEB-08 C00013     A013
    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
    200132       4000 15-AUG-08 C00013     A013
    200100       1000 08-JAN-08 C00015     A003
    200107       4500 30-AUG-08 C00007     A010

See our Model Database



<<PreviousNext>>

 

Looking for some other tutorial?