In this lesson we going learn how to use multiple SQL conditional operators with the WHERE clause. You may know of these as logical operators, since they return either TRUE or FALSE. In the past lesson you learned that rows failing to meet the WHERE clause condition are filtered out. The same concept holds to multiple conditions.
All of the examples in this tutorial are based on PizzaDB. You can get the script to build the PizzaDB here.
For this lesson we continue to use the Product table:
In this example we are querying all Products whose ProductType is ‘P’ and have a Price greater than 12.00.
In general the SQL statement remains the same, the main change is the addition of another conditional operator. Since we are using the AND Boolean operator, both conditions must be true in order for a row to be included in the result.
Can you pick out the various logical operators? The are =, AND, >
Conditional Statements and Boolean Operators
In order to effective write conditional statement it is important to understand how Boolean logical operators affect the condition’s outcome.
| Boolean Operator | Description | Example |
|---|---|---|
| AND | Both conditions must be TRUE for the WHERE condition to be TRUE. | ProductType = ‘P’ and Price > 12 |
| OR | If either comparison is TRUE, the WHERE condition is TRUE. | ProductType = ‘P’ or ProductType = ‘S’ |
| NOT | The comparison is reversed. If TRUE then the result is made FALSE. | NOT (ProductType = ‘P’ or ProductType = ‘S’) |
SQL Conditional Operator Syntax
Here you can see the general setup for use multiple conditions within the WHERE clause.
SELECT column1, column2, ...
FROM TableName
WHERE condition1 AND/OR condition2 AND/OR condition3 ...;Keep in mind that when you’re putting together your AND and OR logic, that you can use parenthesis () to ensure order of precedence. One trick I use to keep things straight is to think of OR as addition and AND as multiplications. In this case you can see that Condition1 AND Condition2 OR Condition 3 is much different than Condition1 AND (Condition2 OR Condition3)
Example Comparing Price to a Range
In this example lets find all products whose prices are between 6 and 12 dollars.
This returns every product within the range. You’ll see in the next example we can shorten with the BETWEEN operator, but for now relish in the fact you’re learning about multiple conditions!
Example Using BETWEEN Operator
In this example let’s see how we can use the BETWEEN operator to simplify our search criteria. Since the BETWEEN operator return all values within in a range, you can use it instead of >= and <= comparisons.
For example here is prior example written using BETWEEN
Writing Price between 6 and 12 is the same as writing Price >= 6 and Price <= 12. I think it make it much easier to read the code.
You can also use BETWEEN with NOT to exclude items from a range. The condition Price NOT BETWEEN 6 and 12, return all values less than 6 or greater than 12.
Example using Three Conditional Operators
In this example let’s find all the Products whose product type is either B or P and Price is less than 10 dollars.
Notice how we’re using parenthesis to ensure our OR clause’s intention is not misinterpreted. One challenge in using SQL Conditional Statements is ensuring the logical statements make sense. As you do the follow exercise pay special attention to which Boolean operators to use and whether you need to enclose any conditions in ().
We can also make this statement easier to use. To do so we can use the IN operator.
Example using IN operator
The IN operator is used to test whether a value is contained with a set of other values. For example writing ProductType in (‘B’,’P’) is testing whether the product type is B or P.
Let’s take our example from above and find all the Products whose product type is either B or P and Price is less than 10 dollars using the IN operator.
One thing to keep in mind is that you can use the NOT operator with IN to exclude items. For example the condition ProductType NOT IN (‘B’,’P’) would return TRUE for all ProductType that aren’t either B or P.
Exercise
Now it is your turn. Let’s see if you can write a query to return all products that are less than 6.00 or greater than 12.00 that aren’t ProductType S or I.
1. Write the without using BETWEEN or IN.
This query can be confusing, especially when you start to work with the <> operator. It you find this one too confusing, then try to use “positive” logic with your comparisons. Meaning, use the = operator as much as possible.
If you follow this advice then another way to write the answer is shown below. Just click Show Answer to see it:
In the next lesson we’re going to look at some more advanced comparison operators. Two in particular, IN and BETWEEN, will help simplify this answer.
2. Write the query using BETWEEN and IN.
Can you write the same query using BETWEEN and IN? Hint: You also need to use the NOT operator.
I