Introduction to Databases for the Web: Pt. 2 - Page 3

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

In

Like BETWEEN, the IN operator is used as a shorthand to specify multiple parameters in one statement. The IN operator looks generically like:

SELECT column_name
FROM table_name
WHERE column_name IN ('value', 'value', value);

The reverse, or course, looks like:

SELECT column_name
FROM table_name
WHERE column_name NOT IN ('value', 'value', value);

As you might have guessed, the IN operator works just the same as stringing multiple OR's together. The list of comma separated values defines a set of acceptable conditions. Thus, to get a listing of all the clients in the 90031 or the 90102 zip codes, you could string together OR operators such as:

SELECT C_NAME, C_ZIP
FROM CLIENTS
WHERE C_ZIP = 90031 
   OR C_ZIP = 90102;

Or using the IN operator, you would have:

SELECT C_NAME, C_ZIP
FROM CLIENTS
WHERE C_ZIP 
   IN ('90031', '90102');

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

C_NAME            C_ZIP
-----------------------
Rick Tan          90031
Stephen Petersen  90102
-----------------------