Datasheet

Writing Simple Queries 37
WHERE d.department_id = e.department_id
AND d.department_name = ‘Administration’);
LAST_NAME FIRST_NAME DEPARTMENT_ID
---------------------- -------------------- -------------
Whalen Jennifer 10
SQL>
IS NULL and IS NOT NULL
To find the NULL values or NOT NULL values, you need to use the IS NULL operator. The = or
!= operator will not work with NULL values. IS NULL evaluates to TRUE if the value is NULL.
IS NOT NULL evaluates to TRUE if the value is not NULL. To find the employees who do not
have a department assigned, use this query:
SELECT last_name, department_id
FROM employees
WHERE department_id IS NULL;
LAST_NAME DEPARTMENT_ID
------------------------- -------------
Grant
SQL>
SELECT last_name, department_id
FROM employees
WHERE department_id = NULL;
no rows selected
LIKE
Using the LIKE operator, you can perform pattern matching. The pattern-search character %
is used to match any character and any number of characters. The pattern-search character
_ is used to match any single character. If you are looking for the actual character % or _ in the
pattern search, you can include an escape character in the search string and notify Oracle
using the
ESCAPE clause.
The following query searches for all employees whose first name begins with Su and last
name does not begin with S:
SELECT first_name, last_name
FROM employees
WHERE first_name LIKE ‘Su%’
AND last_name NOT LIKE ‘S%’;
95127c01.indd 37 2/18/09 6:37:10 AM