Connecting Conditions with Logical Operators

Use the logical operators AND, OR, and NOT when you're dealing with more than one condition in a WHERE clause. The logical operators are also called Boolean operators.

AND

AND joins two or more conditions and returns results only when all of the conditions are true. For example, the following query will find only the rows in which the author's last name is Ringer and the author's first name is Anne. It will not find the row for Albert Ringer.

SQL
select au_id, au_lname, au_fname
from authors
where au_lname = 'Ringer'
  and au_fname = 'Anne'

au_id       au_lname                            au_fname
=========== =================================== ========
899-46-2035 Ringer                              Anne
[1 row]

The next example finds business books with a price higher than $20.00 and for which an advance of less than $20,000 was paid:

SQL
select title, type, price, advance
from titles
where type = 'business'
  and price > 20.00
  and advance < 20000
title                                   type     price  advance
======================================= ======== ====== ========
The Busy Executive's Database Guide     business 29.99   5000.00
Cooking with Computers: Surreptitious
  Balance Sheets                        business 21.95   5000.00
Straight Talk About Computers           business 29.99   5000.00
[3 rows]

OR

OR also connects two or more conditions, but it returns results when any of the conditions is true. The following query searches for rows containing Anne or Ann in the au_fname column:

SQL
select au_id, au_lname, au_fname
from authors
where au_fname = 'Anne'
  or au_fname = 'Ann'
au_id       au_lname                          au_fname
=========== ================================= ==========
899-46-2035 Ringer                            Anne
427-17-2319 Dull                              Ann
[2 rows]

The following query searches for books with a price higher than $20.00 or an advance less than $5,000:

SQL
select title, type, price, advance
from titles
where price > $30.00
  or advance < $5000
title                                      type         price advance
========================================== ============ ===== =======
Secrets of Silicon Valley                  popular_comp 40.00 8000.00
Emotional Security: A New Algorithm        psychology   17.99 4000.00
Prolonged Data Deprivation: Four Case
   Studies                                 psychology   29.99 2000.00
Silicon Valley Gastronomic Treats          mod_cook     29.99    0.00
Fifty Years in Buckingham Palace Kitchens  trad_cook    21.95 4000.00
But Is It User Friendly?                   popular_comp 42.95 7000.00
Is Anger the Enemy?                        psychology   21.95 2275.00
Onions, Leeks, and Garlic: Cooking
   Secrets of the Mediterranean            trad_cook    40.95 7000.00
Computer Phobic and Non-Phobic
   Individuals: Behavior Variations        psychology   41.59 7000.00
[9 rows]

Semantic Issues with OR and AND

One more example using OR will demonstrate a potential for confusion. Let's say you want to find all the business books, as well as any books with a price higher than $10 and any books with an advance less than $20,000. The English phrasing of this problem suggests the use of the operator AND, but the logical meaning dictates the use of OR because you want to find all the books in all three categories, not just books that meet all three characteristics at once. Here's the SQL statement that finds what you're looking for:

SQL
select title, type, price, advance
from titles
where type = 'business'
  or price > $20.00
  or advance < $20000
title                                     type         price advance
========================================= ============ ===== =======
Secrets of Silicon Valley                 popular_comp 40.00  8000.00
The Busy Executive's Database Guide       business     29.99  5000.00
Emotional Security: A New Algorithm       psychology   17.99  4000.00
Prolonged Data Deprivation:
  Four Case Studies                       psychology   29.99  2000.00
Cooking with Computers:
  Surreptitious Balance Sheets            business     21.95  5000.00
Silicon Valley Gastronomic Treats         mod_cook     29.99     0.00
Sushi, Anyone?                            trad_cook    29.99  8000.00
Fifty Years in Buckingham Palace Kitchens trad_cook    21.95  4000.00
But Is It User Friendly?                  popular_comp 42.95  7000.00
You Can Combat Computer Stress!           business     12.99 10125.00
Is Anger the Enemy?                       psychology   21.95  2275.00
Life Without Fear                         psychology   17.00  6000.00
The Gourmet Microwave                     mod_cook     12.99 15000.00
Onions, Leeks, and Garlic:
  Cooking Secrets of the Mediterranean    trad_cook    40.95  7000.00
Straight Talk About Computers             business     29.99  5000.00
Computer Phobic and Non-Phobic
   Individuals: Behavior Variations       psychology   41.59  7000.00
[16 rows]

Compare this query and its results to the earlier example that is identical except for the use of AND instead of OR.

NOT

The logical operator NOT negates an expression. When you use it with comparison operators, put it before the expression rather than before the comparison operator. The following two queries are equivalent:

SQL
select au_lname, au_fname, state
from authors
where state <> 'CA'

SQL
select au_lname, au_fname, state
from authors
where not state = 'CA'

Here are the results:

Results
au_lname                           au_fname        state
================================== =============== =====
Ringer                             Albert          UT
Ringer                             Anne            UT
DeFrance                           Michel          IN
Panteley                           Sylvia          MD
Greene                             Morningstar     TN
del Castillo                       Innes           MI
Blotchet-Halls                     Reginald        OR
Smith                              Meander         KS
[8 rows]

Logical Operator Precedence

Like the arithmetic operators, logical operators are handled according to precedence rules. When both kinds of operators occur in the same statement, arithmetic operators are handled before logical operators. When more than one logical operator is used in a statement, NOT is evaluated first, then AND, and finally OR. Figure 4.5 shows the hierarchy.

Figure 4.5. Precedence Hierarchy for Logical Operators


Some examples will clarify the situation. The following query finds all the business books in the titles table, no matter what their advances are, as well as all psychology books that have an advance greater than $5,500. The advance condition pertains to psychology books and not to business books because the AND is handled before the OR.

SQL
select title_id, type, advance
from titles
where type = 'business'
							  or type = 'psychology'
  and advance > 5500
title_id type              advance
======== ============ ============
BU1032   business          5000.00
BU1111   business          5000.00
BU2075   business         10125.00
PS2106   psychology        6000.00
BU7832   business          5000.00
PS1372   psychology        7000.00
[6 rows]

The results include three business books with advances less than $5,500 because the query was evaluated according to the following precedence rules:

1.
Find all psychology books with advances greater than $5,500.

2.
Find all business books (never mind about advances).

3.
Display both sets of rows in the results.

You can change the meaning of the previous query by adding parentheses to force evaluation of the OR first. With parentheses added, the query executes differently:

1.
Find all business and psychology books.

2.
Locate those that have advances over $5,500.

3.
Display only the final subset.

SQL
select title_id, type, advance
from titles
where (type = 'business' or type = 'psychology')
							and advance > 5500
title_id type              advance
======== ============ ============
BU2075   business         10125.00
PS2106   psychology        6000.00
PS1372   psychology        7000.00
[3 rows]

The parentheses cause SQL to find all business and psychology books and, from among those, to find those with advances greater than $5,500.

Here's a query that includes arithmetic operators, comparison operators, and logical operators. It searches for books that are not bringing in enough money to offset their advances. Specifically, the query searches for any books with gross revenues (that is, ytd_sales times price) less than twice the advance paid to the author(s). The user who constructed this query has tacked on another condition: She wants to include in the results only books published before October 15, 2000, because those books have had long enough to establish a sales pattern. The last condition is connected with the logical operator AND; according to the rules of precedence, it is evaluated after the arithmetic operations.

SQL
select title_id, type, price, advance, ytd_sales
from titles
where price * ytd_sales <  2 * advance
  and pubdate < '10/15/2000'
title_id type        price     advance     ytd_sales
======== =========== ========= =========== ===========
PS2106   psychology  17.00     6000.00     111
[1 row]

SQL VARIANTS

If you run this query on a system with a different date format, you may need to change the pubdate value to correspond to that format. For example, if your SQL engine expects dates to look like DD-MON-YYYY, you could write the query like this:

Oracle
SQL> select title_id, type, price, advance, ytd_sales
  2   from titles
  3   where price * ytd_sales <  2 * advance
  4  and pubdate < '21 OCT 2000';
TITLE_ TYPE             PRICE   ADVANCE YTD_SALES
------ ------------ --------- --------- ---------
PS2106 psychology          17      6000       111


..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.218.89.173