Slide 14.17: The IN operator
  Slide 14.19: Aliases
  Home


The BETWEEN Operator

The BETWEEN ... AND operator selects a range of data between two values. These values can be numbers, text, or dates. 

 SELECT  column_name  FROM  table_name
   WHERE  column_name  BETWEEN  value1  AND  value2

 LastName   FirstName   Address   City 
Hansen Ola Timoteivn 10 Sandnes
Nordmann Anna Neset 18 Sandnes
Pettersen Kari Storgt 20 Stavanger
Svendson Tove Borgvn 23 Sandnes

To display the persons alphabetically between “Hansen” and “Pettersen,” use the following SQL: 

 SQL> select  *  from  Persons
   2    where  LastName  between  'Hansen'  and  'Pettersen';

 LastName   FirstName   Address   City 
Hansen Ola Timoteivn 10 Sandnes
Nordmann Anna Neset 18 Sandnes
Pettersen Kari Storgt 20 Stavanger

To display the persons outside the range used in the previous example, use the NOT operator: 

 SQL> select  *  from  Persons
   2    where  LastName  not between  'Hansen'  and  'Pettersen';

The following results are displayed:

 LastName   FirstName   Address   City 
Svendson Tove Borgvn 23 Sandnes


The following is an SQL test area from w3schools.com where the Customers table may be created by the following command: 
   SQL> create table  Customers (
     2    CustomerID   varchar(16),
     3    CompanyName  varchar(32),
     4    ContactName  varchar(32),
     5    Address      varchar(64),
     6    City         varchar(32),
     7    PostalCode   varchar(16),
     8    Country      varchar(32) );
 Note that the Customers table is for read only.




     






 LastName   FirstName   Address   City 
Hansen Ola Timoteivn 10 Sandnes
Pettersen Kari Storgt 20 Stavanger


The following is an SQL test area from w3schools.com where the Customers table may be created by the following command: 
   SQL> create table  Customers (
     2    CustomerID   varchar(16),
     3    CompanyName  varchar(32),
     4    ContactName  varchar(32),
     5    Address      varchar(64),
     6    City         varchar(32),
     7    PostalCode   varchar(16),
     8    Country      varchar(32) );
 Note that the Customers table is for read only.