 |
|
Oracle Tips by Burleson |
SQL Logical Operators
A SQL query can only have one WHERE clause;
however, that clause can contain multiple comparisons. Each
comparison returns a TRUE, FALSE or NULL. You evaluate these
TRUE/FALSE results using AND and OR to end up with the single TRUE or
FALSE for the entire WHERE clause. The AND operator (called
conjunction) returns TRUE if both comparisons are TRUE and returns
FALSE if either comparison is FALSE.
WHERE salary <
100000 -- TRUE
AND dept = ‘SALES’ -- TRUE
Since both comparisons are TRUE, the WHERE clause
is TRUE. The logical OR operator (called a disjunction) returns TRUE
if either comparison is TRUE , otherwise returns FALSE.
WHERE salary <
100000 -- TRUE
OR dept = ‘SALES’ -- FALSE
The WHERE clause returns TRUE.
Order of Precedence in SQL
It is important to understand how the database
evaluates multiple comparisons in the WHERE clause. All
the AND comparisons (evaluated from Left to Right) are evaluated
before the OR comparisons (evaluated from Left to Right). For
example:
SELECT
Last_name,
Dept,
Salary
FROM
employee
WHERE
dept = ‘FINANCE’
OR
dept = ‘SALES’
AND
salary < 100000;
As you read this query, it appears that you
collect all the employees from the finance and sales department and
then check that the salary is less than 1000000. In this case, you
would be wrong! The database will evaluate the AND before the OR.
What this query actually does is list employees in SALES with salary
< 100000, and all the employees in the FINANCE department.
The above book excerpt is from:
Easy Oracle
SQL
Get Started
Fast writing SQL Reports with SQL*Plus
ISBN 0-9727513-7-8
Col. John Garmany
http://www.rampant-books.com/book_2005_1_easy_sql.htm |
