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
When using both ORDER BY and the WHERE clause the ORDER BY clause should come after the WHERE clause.
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.
|!<||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'
When comparing against string value data type columns quotes are required to surround the values being compared against. For number values quotes are not required.
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
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
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;
When using the AND keyword,note that both conditions need to be true for the data to be returned, otherwise the data is not returned since it does not satisfy all the conditions.
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]%'