Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL etc

Posted Sep 13, 1998

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

By Selena Sol

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


SQL etc Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL etc Forum
Topic By Replies Updated
MySQL rollback UAL225 0 August 21st, 09:56 PM
Complex Search Query Galway 0 May 20th, 10:04 PM
change collation at once supercain 2 May 15th, 06:18 AM
SQL Features, tools and utilities question Neomite 1 April 10th, 09:13 AM