11. Combining Conditions & Boolean Operators
The AND operator can be used to join two or more conditions in the WHERE
clause. Both sides of the AND condition must be true in order for the condition to be met and for those rows to be displayed.
SELECT column1, SUM(column2)
FROM "list-of-tables"
WHERE "condition1" AND "condition2";
The OR operator can also be used to join two or more conditions in the WHERE
clause. However, either side of the OR operator can be true and the condition will be met, hence the rows will be displayed. With the OR operator, either side can be true or both sides can be true.
11.1. Example 1: Using AND Operator
SELECT employeeid, firstname, lastname, title, salary
FROM employee_info
WHERE salary >= 50000.00 AND title = 'Programmer';
This statement will select the employeeid
, firstname
, lastname
, title
, and salary
from the employee_info
table where the salary
is greater than or equal to 50000.00 AND the title
is equal to 'Programmer'. Both of these conditions must be true in order for the rows to be returned in the query. If either is false, then it will not be displayed.
Although they are not required, you can use parentheses around your conditional expressions to make it easier to read:
SELECT employeeid, firstname, lastname, title, salary
FROM employee_info
WHERE (salary >= 50000.00) AND (title = 'Programmer');
11.2. Example 2: Using OR Operator
SELECT firstname, lastname, title, salary
FROM employee_info
WHERE (title = 'Sales') OR (title = 'Programmer');
This statement will select the firstname
, lastname
, title
, and salary
from the employee_info
table where the title
is either equal to 'Sales' OR the title
is equal to 'Programmer'.
11.3. Review Exercises & Answers
Use the tables items_ordered
and customers
for the exercises.
11.3.1. Exercise #1
Select the customerid
, order_date
, and item
from the items_ordered
table for all items unless they are 'Snow Shoes' or 'Ear Muffs'. Display the rows as long as they are not either of these two items.
SELECT customerid, order_date, item
FROM items_ordered
WHERE (item <> 'Snow shoes') AND (item <> 'Ear muffs');
Note: It is correct to use an AND here. If you were to use an OR, then either side of the OR will be true, and every row will be displayed. For example, when it encounters 'Ear muffs', it will evaluate to True since 'Ear muffs' are not equal to 'Snow shoes'.
11.3.2. Exercise #2
Select the item
and price
of all items that start with the letters 'S', 'P', or 'F'.
SELECT item, price
FROM items_ordered
WHERE (item LIKE 'S%') OR (item LIKE 'P%') OR (item LIKE 'F%');