The SQL BETWEEN Operator

The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.

The BETWEEN operator is inclusive: begin and end values are included.

Example

               Selects all products with a price between 500 and 1000:


              SELECT *
               FROM  products 

              
                  WHERE  price  BETWEEN  500 
                  AND  1000 ;

You can click on above box to edit the code and run again.

Output

Syntax

 SELECT  column_name(s)

 FROM  table_name

              
 WHERE  column_name BETWEEN value1  AND  value2;

Demo Products table

This product table is used in examples:

NOT BETWEEN

To display the products outside the range of the previous example, use NOT BETWEEN :

Example

 Return all employee that have NOT placed any order in the products table:
 SELECT *
 FROM  products 
              
 WHERE  price  NOT BETWEEN  500 
 AND  1000 ;

You can click on above box to edit the code and run again.

Output

BETWEEN with IN

The following SQL statement selects all products with a price between 500 and 1000. In addition, the order id must be either 1001,1002, or 1003:

Example

 SELECT *
 FROM  products 

              
 WHERE  price  BETWEEN  500 
 AND  1000 

  AND  Order_id IN ( 1001 , 1002 ,
 1003 );

You can click on above box to edit the code and run again.

Output

BETWEEN Text Values

The following SQL statement selects all products with a Product name alphabetically between Clothes and Kid toys:

Example

 SELECT *
  FROM  products 

              
 WHERE  Product_name  BETWEEN  'Clothes' 
  AND  'Kid toys' 

 ORDER BY  Product_name;

You can click on above box to edit the code and run again.

Output

NOT BETWEEN Text Values

The following SQL statement selects all products with a ProductName not between Clothes and Kid toys:

Example

 SELECT *
 FROM  products 
              
 WHERE  Product_name  NOT BETWEEN  'Clothes' 
 AND  'Kid toys' 
 ORDER BY  Product_name;

You can click on above box to edit the code and run again.

Output

BETWEEN Dates

The following SQL statement selects all orders with an Order date between '01-07-2023' and '30-10-2023':

SELECT * FROM products WHERE Order_date BETWEEN '2023-07-01' AND '2023-10-30';

Example

 SELECT *
 FROM  products 

              
 WHERE  Order_date  BETWEEN  '2023-07-01' 
  AND  '2023-10-30' ;

You can click on above box to edit the code and run again.

Output

This table is used for above example