Skip to content

Latest commit

 

History

History
209 lines (194 loc) · 7.57 KB

4. Filtering.md

File metadata and controls

209 lines (194 loc) · 7.57 KB

Filtering

  • Sometimes we would want to work with every row in a table
    • Purging data to stage new data from a feed
    • Modifying rows in a table after a new column has been added
    • Retrieving all rows from a message queue
  • In most cases you would want to narrow the rows that you want to work with
  • Explore the various types of filtering conditions you can employ in the where clauses of select, update, and delete statements

Condition Evaluation

  • A where caluse can contain one or more conditions, separated by AND or OR
  • If all the conditions where separated with and AND, then all the conditions must be true for the table's row for it to be included int he results set
  • If all the conditions were separated with an OR, then only conditions of the condititions must be true for the row to be included

Using Parentheses

  • If you have three or more conditions in your where clause and they contain both AND and OR, you should use parentheses to make your code clear for anyone reading your code and for the database server
  • WHERE (first_name = 'STEVEN' OR last_name = 'YOUNG') AND create_date > '2006-01-01'

Using the not Operator

  • WHERE NOT (first_name = 'STEVEN' OR last_name = 'YOUNG') AND create_date > '2006-01-01'
  • Only retrieve rows where the first name is not steven or the last name is not young
  • These kind of clauses are not common due to their poor readability
  • It's better to use <>
  • WHERE first_name <> 'STEVEN' AND last_name <> 'YOUNG' AND create_date > '2006-01-01'

Building a Condition

  • A single condition is comprised of one or more expressions combined with one or more operators
  • Expressions can be
    • A number
    • a column in a table or view
    • String
    • built-in function
    • subquery
    • list of expressions
  • Operators can be
    • comparison operators: =,!=,<,>,<>,like,in,between
    • arithemtic operators: +,-,*,/

Condition Types

  • There are different ways of filtering out unwanted data
  • You can look for specific values, sets of values, or range of values to include or exclude
  • You can use pattern-searching techniques to look for partical matches for strings

Equality Conditions

  • column=expression form
  • most common condition you will see
title = 'RIVER OUTLAW'
fed_id = '111-11-111'
amount = 375.25
film_id = (SELECT film_id FROM film WHERE title = 'RIVER OUTLAW')

Inequality conditions

  • Assert that two expressions are not equal
  • <>

Data modification using equality conditions

  • Equality conditions are commonly used when modifying data
  • Example: Removing old account rows once pre year
    • DELETE FROM rental WHERE year(rental_date) = 2004;

Range Conditions

  • You can build conditions that check if an expression falls within a certain range
  • Common type of condition when working with numeric or temporal data
SELECT customer_id, rental_date
FROM rental
WHERE rental_date < '2005-05-25';

SELECT customer_id, rental_date
FROM rental
WHERE rental_date <= '2005-06-16' AND rental_date >= '2005-06-14';

The between operator

  • When you have both upper and lower limits for your range, you may choose to use the BETWEEN operator instead of using a lower than and greater than operator
SELECT customer_id, rental_date
FROM rental
WHERE rental_date BETWEEN '2005-06-14' AND '2995-06-16';
  • When using the BETWEEN operator always keep in mind that you should always specify the lower limit of the range first and then the upper limit of the range
    • AND That the upper and lower limits are inclusive in the range limits
String Ranges
  • In order to work with string ranges, you need to know the order of characters in your character set (the collation of your character set)
/* Return the list of customers with their last names falling between FA and FR */
SELECT last_name, first_name
FROM customer
WHERE last_name BETWEEN 'FA' AND 'FR';

/*Doesn't include the 5 customers whose last names start with FR you inlcude them by extending the righthanded range to FRB*/
SELECT last_name, first_name
FROM customer
WHERE last_name BETWEEN 'FA' AND 'FRB';

Membership Conditions

  • Sometimes you aren't restricting an expression to a value or range of values
  • Somestimes you want to restrict to a finite set of values
SELECT
	title, rating
FROM film
WHERE rating = 'G' OR rating = 'PG';
  • What if we wanted to return rows which matched against of set of 10 or 20 members, that would be to tedious to write with multiple equality conditions
  • We would use the IN operator
SELECT 
	title, rating
FROM film
WHERE rating IN ('G', 'PG');

Using subqueries

  • Use can use a subquery to generate a set of expressions
SELECT
	title, rating
FROM film
WHERE rating in (SELECT rating FROM film WHERE title LIKE '%PET%');

Using not in

  • Somtimes you want to see if an expression doesn't exist within the set
SELECT
	title, rating
FROM film
WHERE rating NOT IN ('PG-13', 'R', 'NC-17');

Matching Conditions

  • The previous conditions can identify an exact string, range of strings or a set of strings
  • This type deals with partial string matches
  • Wildcard characters to build search expressions

Using wildcards

  • What you look for when searching for partial strings
    • Strings starting or end with a character or substring
    • Strings containing a specific character or substring
    • Strings with a specific format
  • Wildcard characters are built with:
    • _ : Match exactly one character
    • % : Any number of characters (including 0)
  • You use the LIKE operator
SELECT
	last_name, first_name
FROM customer
WHERE last_name LIKE '_A_T%S';

SELECT 
	last_name, first_name
FROM customer
WHERE last_name LIKE 'Q%' OR last_name LIKE 'Y%';

Using regular expressions

  • If wildcard characters don't provide enough flexilbility then regular expressions can be used instead
/*Regular expressions are search expressions on `steriods`*/
SELECT
	last_name, first_name
FROM customer
WHERE last_name REGEXP '^[QY]';

Null: That Four-Letter Word

  • null means the abscene of a value but there can be various interpretations of null
    • Not applicable: Employee ID for a transaction that took place at an ATM
    • Value not yet known: When a persons SSN is not yet know when a new employee is added
    • Value undefined: Creating an account for a customer that hasn't been added yet
  • When working with null
    • Expressions can be null, but never equal to null
    • Two null are never equal to each other
  • To test if an expression is null, you need to use the IS NULL operator
SELECT
	rental_id, customer_id
FROM rental
WHERE renturn_date IS NULL;

/*NOte that we use IS NULL instead of = null. = null will return an empty set*/

Test your knowledge

  • Which of the payment IDs would be returned by the following filter condition?
    • customer_id <> 5 AND (amount > 8 OR date(payment_date) = '2005-08-23')
Return the values not equal to customer_id 5 
AND amount > 8 or date equals 23 08

IDs: 101, 107
  • Which of the payment IDs would be returned by the following filter condition?
    • customer_id = 5 AND NOT (amount >6 OR date(payment_date) = '2005-06-19')
return values equal to customer_id 5
AND not values with amount > 6 OR date equals 19-06

IDs: 108, 110, 111, 112, 113, 115, 116, 117, 118, 119, 120
  • Construct a query that retreves all rows from the payments table where the amount is either 1.98, 7.89 or 9.98
SELECT 
	*
FROM payment
WHERE amount IN (1.98, 7.98, 9.98);
  • Construct a query that finds all customers whose last name contains an A in the second position and a W anywhere after the A
SELECT 
	*
FROM customer
WHERE last_name LIKE "_A%W%";