Databases Reference
In-Depth Information
Emp ID Last Name Salary
---------- ------------------------- ----------
100 King 24000
101 Kochhar 17000
102 De Haan 17000
3 rows selected.
A few things come to mind right away. First, King himself is in the list. You
will learn how to remove his name in the next section. Janice could have also
written the
WHERE
clause the other way around:
where 24000 < salary + 10000;
and the results of the query would be the same. Janice could have also saved a bit of
processing time by calculating the salary cutoff number before writing the query:
where salary > 14000;
clause may be about style, readability, and docu-
mentation more than it is about processing speed, which is why the first version
of the
How you write your
WHERE
WHERE
clause might be the best choice.
Column aliases are not allowed in the
WHERE
clause. The actual column names must
be used.
AND
,
OR
, and
NOT
The
clause using comparison operators is really powerful, but in reality,
you usually have more than one condition for selecting rows. Sometimes you
need all of the conditions to be true, sometimes you need only one of the condi-
tions to be true, and sometimes you want to specify what you don't need. You
can accomplish this by using
WHERE
AND
,
OR
, and
NOT
in your
WHERE
clauses.
between two comparison conditions will give you rows
from the table that satisfy both conditions. In one of the queries above, Janice
noticed that King's name was returned in the query that was looking for other
employees who had salaries close to King's. There is no need to include King
in this query. Since Janice knows King's employee ID, she can remove him from
the results of those queries by adding an
Using an
AND
AND condition, as follows:
select employee_id "Emp ID", last_name "Last Name",
salary "Salary" from employees
where salary + 10000 > 24000
and employee_id != 100;
