In the previous examples we have used the equal predicate between all of our expressions. If you wanted to search on two values for JOB, the query could be written in one of two ways. First, with the OR operator:

WHERE JOB = 'Slsm' ç qualifier

OR JOB = 'Clrk'; ç qualifier

Secondly, with the IN operator. The IN operator allows you to specify a list of values. When one of those values is true the row will be returned to the results table. The SQL implementation for using the IN operator is below:

WHERE JOB IN ('Slsm','Clrk'); ç qualifying values for IN

The results tables from the two previous queries would be identical:

These queries could also be reversed or negated by using not equal or NOT IN for the search condition. Because keyboards vary, ORACLE allows you to use any one of three different not equal operators: !=, <>, or ^=. You would use one of these operators when you wanted to select rows that don't meet certain search conditions. To return all employees who are not a salesman or a clerk, either NOT IN or NOT EQUAL may be used. The SQL format for NOT IN is as follows:

WHERE JOB NOT IN ('Slsm','Clrk'); ç NOT IN predicate

WHERE JOB != 'Slsm' ç NOT EQUAL predicates

The results table would look like this.

Suppose you want to list all employees who are not in a specific department, say department 10, but who are clerks.

WHERE JOB = 'Clrk' ç equal AND

AND DEPTNO ^= 10; ç NOT EQUAL predicates

The results table would look like this: 

Searching for a value in a range uses the BETWEEN predicate. This operator lets you select all the rows from a table where the values are within the range specified. The use of NOT BETWEEN would search for all values which do not fall within the specified range. To list all employees whose salary is between 825 and 2300 the query would be formatted as follows:

WHERE SALARY BETWEEN 825 AND 2300; ç BETWEEN predicate

The BETWEEN predicate is an inclusive operator. It will return all the rows in which salary is greater than or equal to 825 and less than or equal to 2300.

The results table would look like this:

The use of the BETWEEN predicate is more efficient than the combination of the greater than or equal and the less than or equal predicates because of the access path that ORACLE selects. An example of a query written without the BETWEEN predicate is shown below:

The results table would be identical to the one above, but the second query would require more system resources.

The use of BETWEEN, NOT BETWEEN, IN, NOT IN and the combination of less than or equal and greater than or equal are all operators which test for more than a single value. The conditional operators which can be used in ORACLE to test for a single value are: is equal to (=), is greater than (>), is less than (<), is not equal (<>,!=,^=), is greater than or equal (>=), is less than or equal (<=), LIKE, NOT LIKE, EXISTS, NOT EXISTS, IS NULL and IS NOT NULL.

All of the single value operators except EXISTS, NOT EXISTS, LIKE, NOT LIKE, IS NULL and IS NOT NULL use the same format in their queries. For example:

The operator LIKE performs pattern matching. LIKE will search through the database column specified in the first expression to look for values that look like the pattern indicated in the second expression. Two special characters are used to indicate what type of pattern match is desired: % and _. The % is called a wild card, the _ (underscore) is a position marker. To look for all employees whose name begins with R, use the following query:

The % means anything after the R is okay. All rows which have a NAME beginning with R will be returned. It does not matter how many characters go behind it. The results table from the above query is as follows:

The use of % is very versatile. Several %'s can be combined in one expression; it can be placed in the first position (see note), the last position or anywhere in the middle. Following are some illustrations of where clause possibilities using LIKE:

This query produces the following results table:

The results table from this query would look like this:

The positional marker, underscore (_), is used to find characters in specified positions. To illustrate a search for the letter 'i' in the fourth position the syntax is as follows:

WHERE NAME LIKE '___i%'; ç LIKE predicate

The three underscores represent any three characters in the first three positions, but an 'i' must be in the fourth position and any number of characters can follow it. Ramirez is the only employee returned from the employee table since that is the only name which fits the pattern specified.

Null tests to see if data exists within a column. If the value of a column for one row is empty it is said to be NULL. NULL means data is missing or unknown; it does not mean the value is equal to zero. At the time of table creation, one may specify whether null values are allowed in each column. If null values are allowed in a column then insertions into the table can be done without populating that column. Why would you want to do this? Take for example the EMPLOYEE table which has a column specified as COMM for commission. Not all of the employees earn commissions; therefore the column would be null for non-commission employees but would be populated for employees paid on commission.

Format:

To list all employees who have earned a commission to date:

WHERE COMM IS NOT NULL ç NOT NULL operator

The results tables would look like this:

NAME

--------

Smith

Allen

Schwartz

The EXISTS and NOT EXISTS operators will be covered in SQL Exercise 9 - Joins and Subqueries.

For you to try:

1. Write a SQL query to find employee number, name and job of all females who are not managers.
2. Write a SQL query to find employee number, name and salary of all employees who make less than $1000 or more than $4000, ordered by salary.
3. Write a SQL query to find the name and employee number of all salesmen whose name begins with the letter S.
4. Use the IN operator to write a SQL query to find the name and employee number of all employees who are analysts or managers, ordered alphabetically by name.
5. Write a SQL query to find the employee number, name, salary and commision of all employees who have been paid commission and whose salary is greater than $4000.

Go here to check your answers.

Last revised 10/21/97 by J. Warren, Kennesaw State University,

Department of Computer Science and Information Systems