WHERE Clause

1. WHERE Clause Chapter 2

2. Objectives • Limit rows by using a WHERE clause • Use the LIKE operator • Effect of NULL values • Use compound conditions • Use the BETWEEN and IN operators

3. WHERE clause • Basic SELECT … FROM statement with no WHERE clause returns all rows from table • Limit rows by using a WHERE clause • WHERE clause may include one or more conditions • A condition is made up of: an expression, one or more operators and an expression • An expression may involve column(s) and/or literal(s) and arithmetic operators and … • Some operators (comparison) include: = > < != <>

4. WHERE clause using Comparison Operators • Example 1: SELECT employee_id, department_id FROM employees WHERE department_id = 90; • Example 2: SELECT employee_id, department_id FROM employees WHERE (salary+commission_pct) > salary; • NOTE: a result of “No rows selected” means no data satisfied the criteria specified in WHERE clause

5. Comparison Operators (ctd) • Example using != (not equal operator): SELECT employee_number, department_id FROM employees WHERE department_id != 10; • Example using <> (also a not equal operator): SELECT employee_number, department_id FROM employees WHERE department_id <> 10;

6. LIKE operator • LIKE operator is used when exact match with equal operator cannot be used; i.e. to to match partial strings of character data • LIKE is normally used with wildcard symbols: • An % represents 0, 1 or more characters • An _ represents exactly one character position • Example of finding employees whose last name starts with a H: SELECT last_name FROM employees WHERE last_name LIKE ‘H%’

7. LIKE operator (ctd) • Example - find employees whose last name starts with Hi: SELECT last_name FROM employees WHERE last_name LIKE ‘Hi%’ • Example - find employees whose last name ends with s: SELECT last_name FROM employees WHERE last_name LIKE ‘%s’

8. LIKE operator (ctd) • Example - find employees whose last name contains an a: SELECT last_name FROM employees WHERE last_name LIKE ‘%a%’ • Example - find employees whose last name has an a as its’ second letter: SELECT last_name FROM employees WHERE last_name LIKE ‘_a%’

9. LIKE operator (ctd) • Exercise - find employees whose last name starts with an H and has an i as second-last letter. SELECT last_name FROM employees WHERE last_name LIKE ‘H%i_’

10. NULL value • A NULL value is not actually stored as data and the comparison operators of =, >, <, !=, <> will not return rows containing null values • Example: list employee# and commission rates for all employees whose commission rate is less than 1: SELECT employee_id, commission_pct FROM employees WHERE commission_pct < 1 NOTE many employees are not included in the result because their commission rate is NULL • To refer to a NULL value in a comparison condition the IS operator must be used (i.e. IS NULL or IS NOT NULL)

11. NULL value (ctd) • Example - list all employees with no value for commission rate: INCORRECT (syntactically validbut produces incorrect output): SELECT employee_id, commission_pct FROM employees WHERE commission_pct = NULL CORRECT (syntactically valid ANDproduces correct output): SELECT employee_id, commission_pct FROM employees WHERE commission_pct IS NULL

12. NULL value (ctd) • Example - list all employee with a value for commission rate : INCORRECT (syntactically validbut produces incorrect output): SELECT employee_id, commission_pct FROM employees WHERE commission_pct != NULL CORRECT (syntactically valid ANDproduces correct output): SELECT employee_id, commission_pct FROM employees WHERE commission_pct IS NOT NULL

13. NULL value (ctd) • Where are NULL values placed with an ORDER BY clause? • Example: list all employees and their commission rates with the employees paid the lowest commission rates listed first: SELECT last_name, first_name, commission_pct FROM employees ORDER BY commission_pct; Now list employees with highest comm rate first: SELECT last_name, first_name, commission_pct FROM employees ORDER BY commission_pct DESC; • From looking at the outputs, where are NULL values placed with an ORDER BY clause?

14. Compound Conditions • Compound conditions are formed by connecting two or more simple conditions using logical operators of AND, OR, and NOT • AND: all conditions must be true in order for compound condition to be true • OR: if any one of conditions is true then compound condition is true • NOT: reverses the value of the original condition • Standard order of precedence is used but it is strongly recommended that parentheses be used when compound condition includes both ANDs and ORs to insure correct understanding of order of evaluation

15. Compound conditions (ctd) • Example: display names of employees whose salary is over $5000 and who are located in 20 or 10: SELECT last_name FROM employees WHERE salary > 5000 AND (department_id = 20 OR department_id = 10) • NOTE: the following statement produces different results: SELECT last_name FROM employees WHERE salary > 5000 AND department_id = 20 OR department_id = 10

16. BETWEEN Operator • The BETWEEN operator is used to easily specify a range of values as a condition, i.e. BETWEEN lower_value AND upper_value, where the range includes the lower and upper values • Example: SELECT employee_id, salary FROM customer WHERE salary BETWEEN 5000 AND 10000 • Could also be accomplished by: SELECT employee_id, salary FROM employees WHERE salary >= 5000 AND salary <= 10000

17. IN Operator • IN operator is used to easily specify a list of values as a condition • Example: SELECT employee_id, department_id FROM employees WHERE department_id IN (10, 20, 30) • Could also be accomplished by: SELECT employee_id, department_id FROM employees WHERE department_id = 10 OR department_id = 20 OR department_id = 30