SQL Basics - The WHERE Clause (AND OR and IN)

CHECKING FOR MULTIPLE CONDITIONS

In last lesson we learned about the WHERE clause used to filter the results. How about if you wanted to filter the results further so that the output matches in two aspects. For this we use the AND operator. For example:

SELECT prod_name FROM Products
WHERE vend_id='BRS01' AND prod_price<9;

This returns:

It returned only the name of the products that are from vendor BRS01 and that are priced under $9.00.

___________________________________________________

CHECKING FOR ONE OF MULTIPLE CONDITIONS

Now if we wanted to retrieve items that match one of various conditions, we would use the OR operator, like this:

SELECT * FROM OrderItems
WHERE quantity>50 OR item_price>10;

Here's what you get:

You can also use both AND and OR together. The AND will get processed first, and the OR second, so to use the OR first use parentheses, as shown below.

SELECT * FROM TeethEyes WHERE child_age<6 AND child_eyes='Gray' OR child_eyes='Brown'

SELECT * FROM TeethEyes WHERE child_age<6 AND (child_eyes='Gray' OR child_eyes='Brown')

In the first case we didn't use parentheses. It returned all entries that match either a child under 6 with brown eyes, or a child with gray eyes. In the second case, it returned children under 6 that have either Gray or Brown eyes.

___________________________________________________

A similar operator is the IN operator. It works almost the same as OR, with a different syntax.

SELECT * FROM TeethEyes
WHERE child_age IN(8,1,10)

___________________________________________________

Before we close out for the day, I'll mention two more operators, NOT and IS NULL. NOT works almost the same as'<>', only it comes before the column name.

SELECT vend_name,vend_country FROM Vendors
WHERE NOT vend_country='USA'

IS NULL checks for a NULL value in the given column. In our Vendors table, foreign companies don't have states listed so this is the result.

SELECT vend_name,vend_country FROM Vendors
WHERE vend_state IS NULL