# SQL Conditional Operators

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.

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.

select ProductID, ProductName, ProductType, Price from Product where ProductType = 'P' and Price > 12
```/* Answer */
select ProductID, ProductName, ProductType, Price
from Product
where ProductType = 'P' and Price > 12```

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.

## 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.

select ProductID, ProductName, ProductType, Price from Product where Price >= 6 and Price <= 12
```/* Answer */
select ProductID, ProductName, ProductType, Price
from Product
where Price >= 6 and Price <= 12```

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

select ProductID, ProductName, ProductType, Price from Product where Price between 6 and 12
```/* Answer */
select ProductID, ProductName, ProductType, Price
from Product
where Price between 6 and 12```

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.

select ProductID, ProductName, ProductType, Price from Product where (ProductType = 'B' or ProductType = 'P') and Price < 10
```/* Answer */
select ProductID, ProductName, ProductType, Price
from Product
where (ProductType = 'B' or ProductType = 'P') and Price < 10```

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.

select ProductID, ProductName, ProductType, Price from Product where ProductType in ('B','P') and Price < 10
```/* Answer */
select ProductID, ProductName, ProductType, Price
from Product
where ProductType in ('B','P') and Price < 10```

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.

```/* Answer */
select ProductID, ProductName, ProductType, Price
from Product
where (ProductType <> 'S' and ProductType <> 'I') and (Price < 6 or Price > 12)```

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.

```/* Answer */
select ProductID, ProductName, ProductType, Price
from Product
where not (ProductType = 'S' or ProductType = 'I') and (Price < 6 or Price > 12)```

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.

```/* Answer */
select ProductID, ProductName, ProductType, Price
from Product
where ProductType not in('S', 'I') and price not between 6 and 12```

I