In the
example, the SELECT statement retrieves the employee ID, last name, job ID, and
department number of all employees who are in department 90.
Note: You cannot use column alias in the
WHERE clause.
SELECT
employee_id, last_name, job_id, department_id FROM employees WHERE department_id = 90 ;
Character
Strings and Dates
Character
strings and dates in the WHERE clause must be enclosed with single quotation
marks (''). Number constants, however, should not be enclosed with single
quotation marks.
All
character searches are case-sensitive. In the following example, no rows are
returned because the EMPLOYEES table stores all the last names in mixed case:
SELECT last_name, job_id,
department_id
FROM employees
WHERE last_name = 'WHALEN';
Oracle
databases store dates in an internal numeric format, representing the century,
year, month, day, hours, minutes, and seconds. The default date display is in
the DD-MON-RR format.
Comparison
Operators
Comparison
operators are used in conditions that compare one expression to another value
or expression. They are used in the WHERE clause in the following format:
Syntax
... WHERE expr operator value
Example
... WHERE hire_date = '01-JAN-95'
... WHERE salary >= 6000
... WHERE last_name = 'Smith'
An alias
cannot be used in the WHERE clause.
Note: The symbols != and ^= can also represent the not equal to
condition.
Using
Comparison Operators
In the
example, the SELECT statement retrieves the last name and salary from the
EMPLOYEES table for any employee whose salary is less than or equal to $3,000.
Note that there is an explicit value supplied to the WHERE clause. The explicit
value of 3000 is compared to the salary value in the SALARY column of the EMPLOYEES
table.
SELECT
last_name, salary
FROM employees
WHERE salary <= 3000 ;
Range
Conditions Using the BETWEEN Operator
You can
display rows based on a range of values using the BETWEEN operator. The range
that you specify contains a lower limit and an upper limit.
SELECT last_name
FROM employees
WHERE salary BETWEEN 2500 AND 3500
FROM employees
WHERE salary BETWEEN 2500 AND 3500
The SELECT
statement above returns rows from the EMPLOYEES table for any employee whose
salary is between $2,500 and $3,500.
Values that
are specified with the BETWEEN operator are inclusive. However, you must
specify the lower limit first.
You can
also use the BETWEEN operator on character values:
SELECT last_name
FROM employees
WHERE last_name BETWEEN 'King' AND 'Smith';
FROM employees
WHERE last_name BETWEEN 'King' AND 'Smith';
Membership
Condition Using the IN Operator
The IN
operator can be used with any data type. The following example returns a row
from the EMPLOYEES table, for any employee whose last name is included in the
list of names in the WHERE clause:
SELECT employee_id,
manager_id, department_id
FROM employees
WHERE last_name IN ('Hartstein', 'Vargas');
If
characters or dates are used in the list, they must be enclosed with single
quotation marks ('').
Note: The IN operator is internally
evaluated by the Oracle server as a set of OR conditions, such as a=value1 or
a=value2 or a=value3. Therefore, using the IN operator has no performance
benefits and is used only for logical simplicity.
Pattern
Matching Using the LIKE Operator
You may not
always know the exact value to search for. You can select rows that match a
character pattern by using the LIKE operator. The character pattern–matching
operation is referred to as a wildcard search. Two symbols can be used
to construct the search string.
The LIKE
operator can be used as a shortcut for some BETWEEN comparisons. The following
example displays the last names and hire dates of all employees who joined
between January, 1995 and December, 1995:
SELECT last_name,
hire_date
FROM employees
WHERE hire_date LIKE '%95';
Combining
Wildcard Characters
The % and _
symbols can be used in any combination with literal characters
ESCAPE
Identifier
When you
need to have an exact match for the actual % and _ characters,
use the ESCAPE identifier. This option specifies what the escape character is.
If you want to search for strings that contain SA_, you can use the following
SQL statement:
SELECT employee_id,
last_name, job_id
FROM employees WHERE job_id LIKE '%SA\_%' ESCAPE '\';
Using
the NULL Conditions
The NULL
conditions include the IS NULL condition and the IS NOT NULL condition.
The IS NULL
condition tests for nulls. A null value means that the value is unavailable,
unassigned, unknown, or inapplicable. Therefore, you cannot test with =,
because a null cannot be equal or unequal to any value.
To display
the last name, job ID, and commission for all employees who are not
entitled to receive a commission, use the following SQL statement:
SELECT last_name, job_id,
commission_pct
FROM employees
WHERE commission_pct IS NULL;
Defining
Conditions Using the Logical Operators
A logical
condition combines the result of two component conditions to produce a single
result based on those conditions or it inverts the result of a single
condition. A row is returned only if the overall result of the condition is
true.
Three
logical operators are available in SQL:
• AND
• OR
• NOT
All the
examples so far have specified only one condition in the WHERE clause. You can
use several conditions in a single WHERE clause using the AND and OR operators.
Using
the AND Operator
In the
example, both the component conditions must be true for any record to be
selected. Therefore, only those employees who have a job title that contains
the string ‘MAN’ and earn $10,000 or more are selected.
All
character searches are case-sensitive, that is no rows are returned if ‘MAN’ is
not uppercase. Further, character strings must be enclosed with quotation
marks.
SELECT
employee_id, last_name, job_id, salary
FROM employees
WHERE salary >= 10000
AND job_id LIKE '%MAN%' ;
Using
the OR Operator
In the
example, either component condition can be true for any record to be selected.
Therefore, any employee who has a job ID that contains the string ‘MAN’ or
earns $10,000 or more is selected.
SELECT
employee_id, last_name, job_id, salary
FROM employees
WHERE salary >= 10000
OR job_id LIKE '%MAN%' ;
Note: The NOT operator can also be used
with other SQL operators, such as BETWEEN, LIKE, and NULL.
... WHERE job_id
NOT IN ('AC_ACCOUNT', 'AD_VP')
... WHERE salary
NOT BETWEEN 10000 AND
15000
... WHERE last_name NOT
LIKE '%A%'
... WHERE commission_pct IS
NOT NULL
SELECT
last_name, job_id
FROM employees
WHERE job_id
NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP')
;
