Now You Try It
Change this SELECT statement from AND to OR.
Your result looks like this:
EMP_ID CITY PHONE ------ ---- ----- 2299 Canton <null> 4773 Medford 5083847566 3338 Canton 6179238844 2246 Medford 5083457789 1034 Framingham 6172251178 2424 Natick 5083347700 3767 Natick 5082844094 2898 Medford 6173458860 3449 Concord 5082684508 3082 Camden 5089273644 3341 Newton 6179658099 4660 Framingham 5092344620 2209 Brookline 6175563331 2894 Taunton 5088449008 4001 Natick 5082649956 5090 Canton 5083389935 1765 Grover 5087394772 4456 Newton 6179660089 2145 Wilmington 5087486625 3991 Taunton 5081840883 3778 Medford 6173450099 4358 Wilmington 5087488011 4962 Boston 6177732280 2180 Medford 5083145366 2106 Medford 5083346364 3222 Brookline 6176635520 4002 Wilmington 5088477701 2437 Boston 6179264105 2096 Brookline 6175553643 2004 Medford 5089253998 5103 Brookline 6176600684 5008 Brookline 6176642209 4321 Grover 5087463300 2598 Camden <null> 3764 Brookline 6179458377 2461 Medford 5083873664 2448 Natick 5082844736 1003 Boston 6173295757 1234 Brookline 6176646602 2466 Medford 5089487709 4027 Natick 5089445386 2174 Brookline 6176633854 2781 Stoneham 6173286008 3704 Dedham 6177739440 4008 Brookline <null> 3841 Boston 6173298763 3433 Newton 6178653440 3288 Newton 6179654443
4703 Brookline 6176648290 3294 Brookline 6175567551 3118 Canton 5083766984 3769 Grover 5084850432 52 rows processed
If your results do not match what you see above, check Review Answers for Chapter 4 for the correct SQL syntax. Remember, result tables may be shortened in this guide.
Compare the result of the first SELECT statement with the result of the second.
The first SELECT statement gives a more limited list because an employee must both live in one of the three communities and have a telephone in order to be included in the list.
The second SELECT statement results in a longer list because an employee must either live in one of the three communities or have a telephone (and live anywhere) in order to be included. Thus, the result table lists everyone who has a telephone and everyone who lives in the three communities.
Try One with Both AND and OR
You can have multiple predicates, connecting them with either AND or OR.
The Human Resources department needs a list of employees who have a telephone and live in Brookline and all employees who live in Boston regardless of whether they have a telephone.
To produce this list, enter:
select emp_id, city, phone
from employee
where phone is not null and city = 'Brookline'
or city = 'Boston';
The result looks like this:
EMP_ID CITY PHONE ------ ---- ----- 3411 Boston <null> 2010 Boston <null> 2209 Brookline 6175563331 4962 Boston 6177732280 3222 Brookline 6176635520 2437 Boston 6179264105 2096 Brookline 6175553643 5103 Brookline 6176600684 5008 Brookline 6176642209 3764 Brookline 6179458377 1003 Boston 6173295757 1234 Brookline 6176646602 2174 Brookline 6176633854 3841 Boston 6173298763 4703 Brookline 6176648290 3294 Brookline 6175567551 16 rows processed
Using Parentheses
The default order of evaluation is AND before OR. You use parentheses to override the default order of evaluation. Multiple search conditions enclosed in parentheses are evaluated as a single search condition.
How It's Done
If the Human Resources department wants a list of employees living in Brookline or Boston who have a telephone, you would insert parentheses to group the Brookline and Boston predicates. The parentheses specify that you want the OR portion of the clause to be evaluated first:
select emp_id, city, phone
from employee
where phone is not null
and (city = 'Brookline' or city = 'Boston');
The result looks like this:
EMP_ID CITY PHONE ------ ---- ----- 2209 Brookline 6175563331 4962 Boston 6177732280 3222 Brookline 6176635520 2437 Boston 6179264105 2096 Brookline 6175553643 5103 Brookline 6176600684 5008 Brookline 6176642209 3764 Brookline 6179458377 1003 Boston 6173295757 1234 Brookline 6176646602 2174 Brookline 6176633854 3841 Boston 6173298763 4703 Brookline 6176648290 3294 Brookline 6175567551 14 rows processed
Compare this result with the result from the previous statement. The previous SELECT statement without parentheses listed employees who have a telephone and who also live in Brookline as well as employees who live in Boston whether or not they have a telephone.
The second SELECT statement with parentheses listed employees who live in either Brookline or Boston and who have a telephone, no matter which community they live in.
When you create a complex combination of predicates as in the last example, use parentheses to group predicates and establish the order of evaluation.
The placement of Parentheses
You've just seen that using parentheses can make a difference in the order in which the predicates are evaluated.
Look at these two SELECT statements. They are exactly the same except for the placement of the parentheses.
Enter both SELECT statements and compare the results:
1. select emp_id, phone, city, dept_id
from employee
where phone is not null
or (city = 'Boston' and dept_id = 5200);
2. select emp_id, phone, city, dept_id
from employee
where (phone is not null or city = 'Boston')
and dept_id = 5200;
Now take all parentheses out of the request.
How are the results different?
The first SELECT statement specifies that the employee must either live in Boston and work in department 5200 or have a telephone in order to be on the list.
The second SELECT statement specifies that the employee must either have a telephone or live in Boston. In either case, the employee must also work in department 5200 in order to be placed on the list.
If you take out all the parentheses, SQL evaluates the conditions in the same order as for the first SELECT statement.
|
Copyright © 2014 CA.
All rights reserved.
|
|