Filtering Data

Limiting data retrieved using the WHERE Clause

The WHERE clause is used to filter the data returned from the database. You specify the columns and conditions to limit the data that will be returned.

The basic syntax for the WHERE clause is as follows :

SELECT <columnN>, <columnN2>, <columnN*>
FROM <table>
WHERE <column> = <condition> [AND|OR <column> = <condition>]

Return all rows with a specific product price

We can return all rows with product price equal to 3.49 using the following statement with a WHERE clause.

SELECT prod_name, prod_price
FROM products
WHERE prod_price = 3.49

The WHERE Clause Operators

The WHERE clause supports a lot of different types of operators other than the simple equality operator we have used thus far.

Operator	Description
=	Equality
<>	Not equal
!=	Not equal
<	Less than
>	Greater than
!<	Not less than
!>	Not greater than
>=	Greater than or equal to
<=	Less then or equal to
BETWEEN	Between two specified values
IS NULL	Is a NULL value

Retrieve all product less than 10

SELECT prod_name, prod_price
FROM products
WHERE prod_price < 10

Retrieve all products less than or equal to 10

SELECT prod_name, prod_price
FROM products
WHERE prod_price <= 10

Retrieve all products that does not match

We want to retrieve all product not made by a specific vendor

SELECT vend_id, prod_name
FROM products
WHERE vend_id <> 'DLL01'

Retrieving values within a range

The BETWEEN keyword is used to retrieve values between two values, and the AND keyword is used to separate the two values. The values will be matched inclusive of the start and end values.

SELECT prod_name, prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10

Checking for no value

When creating a table, you can specify that a specific column might not contain a value. You use the NULL keyword to specify that condition. We can use the equal sign operator to check for a NULL value, we use the keywords IS NULL.

Lets find all products without prices

SELECT prod_name
FROM products
WHERE prod_price IS NULL

Find all customers without an email address

SELECT cust_name
FROM customers
WHERE cust_email IS NULL

Combining WHERE Clauses

We can combine multiple criteria in the WHERE clause by using AND and OR keywords to create more powerful filters.

Retrieve data that matches two columns

The statement below will retrieve all products made by vendor with vendor id DLL01 and also having price less than or equal to 4.

SELECT prod_id, prod_price, prod_name
FROM products
WHERE vend_id = 'DLL01' AND prod_price <= 4;

Selecting either using OR keword

The OR keyword is the opposite of the AND keyword. It returns when either one of the conditions is true.

The statement below will return all products by name and price made by either vendor with id DLL01 or BRS01 but not both.

SELECT prod_name, prod_price
FROM products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01'

Order of evaluation

When specifying multiple conditions use the parentheses to group the conditions together otherwise you will get unexpected results.

The following sql statements will not return the expected results :

SELECT prod_name, prod_price
FROM producs
WHERE vend_id = 'DLL01' OR vend_id = '  BRS01' AND prod_price >= 10;

You will notice the query will also return products with prices less than 10. This is because the AND operator is being evaluated first.

To return the correct results we will need to use the brackets to wrap around the conditions.

SELECT prod_name, prod_price
FROM producs
WHERE (vend_id = 'DLL01' OR vend_id = '  BRS01') AND prod_price >= 10;

Using the IN Operator

The IN operator is used to specify a range of operators, any of which can be matched. The IN takes a comma separated list of valid values, all enclosed in parentheses.

SELECT prod_name, prod_price
FROM products
WHERE vend_id in ('DLL01', 'BSR01')

The IN operator works the same as using multiple OR statements. We could have returned the same results using the OR operator as follows :

SELECT prod_name, prod_price
FROM products
WHERE vend_id = 'DLL01' OR vend_id =  'BSR01'

The IN operator enables you to embed another sql statement instead of just specifying the values.

Using the NOT Operator

The NOT operator negates the conditions. The NOT operator is always used with another operator.

Lets list all products but exclude products made by a specific vendor :

SELECT prod_name
FROM products
WHERE NOT vend_id = 'DLL01'

Instead of using the NOT operator we could have returned the same results but using a slighlt different statement

SELECT prod_name
FROM products
WHERE NOT vend_id <> 'DLL01'

NOT becomes powerful in complicated sql statements, where multiple conditions are specified, e.g. if we did want to match a specific list of conditions, we could precede the IN keyword with the NOT operator.

Using the LIKE predicate

The LIKE predicate is used to search for unknown data, unlike the equality operator that is used to search for known values. The LIKE predicate is used together with characters that have special meaning.

Percent Sign (%) Wildcard

The most frequently used wildcard is the percent sign (%). Within a string, the % means, match any number of number of occurrences of any character.

The following sql statement finds all products with name that begins with Fish following by any character

SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE 'Fish%'

The wildcard character can be used anywhere within the search pattern. We can also use multiple wildcard characters.

The following sql statement matches all products that have the name bean bag within them regardless of any character after of before the text.

SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '%bean bag%'

The % does not match a NULL value. Be careful when using it with columns that have a NULL value as it will not match it.

The underscore(_) wildcard

The underscore(_) will match only a single character instead of many.

The brackets([]) wildcard

The brackets will match either of the characters inside the brackets. The following statement will match any customer contacts that have a name that either starts with with J or M followed by any character.

SELECT cust_contact
FROM customers
WHERE cust_contact LIKE '[JM]%'

The wildcard can also be negated to not match the characters in the brackets using the ^ character, e.g

SELECT cust_contact
FROM customers
WHERE cust_contact LIKE '[^JM]%'