Between

Like AND, OR and NOT, the BETWEEN operator is used to modify the WHERE clause. The BETWEEN operator works much like the combination of >=, AND, and <=. The fact is that, such circumstances arose so frequently, that the developers of SQL simply made a shortcut for the operation. Thus, to get a listing of all the employees with salaries between the range of 30,000 and 60,000, you could use the long hand version such as:

SELECT EMP_NAME, EMP_SALARY
FROM EMPLOYEES
WHERE EMP_SALARY >= 30000 AND EMP_SALARY <= 60000;

Or, you could use the BETWEEN operator such as:

SELECT EMP_NAME, EMP_SALARY
FROM EMPLOYEES
WHERE EMP_SALARY BETWEEN  30000 AND 60000;

In either case, you'd get the following results:

EMP_NAME	EMP_SALARY
-------------------------------
Lim Sing Yuen	40000
Loo Soon Keat	50000
-------------------------------

As you can see, the BETWEEN operator is mainly a convenience operator to allow you to type less. As you might expect, the BETWEEN operator comes with its sister NOT BETWEEN operator. Thus, you could get all the employees who make more than 60,000 or who make less than 45,000 using:

SELECT EMP_NAME, EMP_SALARY
FROM EMPLOYEES
WHERE EMP_SALARY NOT BETWEEN 45000 AND 60000;

In this case, you'd get the following results:

EMP_NAME	EMP_SALARY
-------------------------------
Lim Li Chuen	90000
Lim Sing Yuen	40000
-------------------------------

And, Or, and Not
Introduction to Databases for the Web | Table of Contents
In