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