SQL Where Examples

Let’s continue with more SQL WHERE examples.  In this lesson, you learn to use the AND, OR, and NOT operators to string together multiple conditions to create queries satisfying several search criteria. 

Recall that the WHERE clause returns a row whenever the where condition is true. 

When using just one column there is no need to concern yourself with Boolean logic, but it is when working with multiple columns. 

For the lesson, we’ll focus on the Purchasing.Vendor table. 

SQL WHERE Examples with AND 

If you want to match two columns, then use the AND operator.  For example, this SQL returns all vendors that have a  PreferredVendorStatus and CreditRating of 2.

/* Answer */
SELECT AccountNumber
	 , Name
	 , CreditRating
	 , PreferredVendorStatus
	 , ActiveFlag
	 , ModifiedDate
FROM Purchasing.Vendor
WHERE PreferredVendorStatus = 1 AND 
	  CreditRating = 2;

Let’s see you give one a try.  How would you change the SQL to also include vendors with and ActiveFlag=1?

SELECT AccountNumber, Name, CreditRating, PreferredVendorStatus, ActiveFlag, ModifiedDate 
  FROM Purchasing.Vendor 
 WHERE PreferredVendorStatus = 1 AND CreditRating = 2 AND ActiveFlag=1

SQL WHERE Examples with OR 

When you use the OR operator, a row is included in the result if at least once of the conditions is TRUE. 

For example, to find all vendors with a CreditRating of 4 or that are inactive.

SELECT AccountNumber
	 , Name
	 , CreditRating
	 , PreferredVendorStatus
	 , ActiveFlag
	 , ModifiedDate
FROM Purchasing.Vendor
WHERE CreditRating = 4 OR 
	  ActiveFlag = 0;

In this case, when either the CreditRating is 4 or the ActiveFlag is 0 a row is returned.  Unlike the AND operator, BOTH conditions do not need to evaluate to TRUE. 

As you continue to learn SQL you’ll see there are many ways you can write a query to get the same result.  Previously we learned about the IN operator. 

If we need to find all vendors with a credit rating of 1-5 we can write:

SELECT AccountNumber
      , Name
      , CreditRating
      , PreferredVendorStatus
      , ActiveFlag
      , ModifiedDate
 FROM Purchasing.Vendor
 WHERE CreditRating IN( 1, 2, 3, 4, 5 ); 

But the same can also be written using OR operators as 

SELECT AccountNumber
      , Name
      , CreditRating
      , PreferredVendorStatus
      , ActiveFlag
      , ModifiedDate
 FROM Purchasing.Vendor
 WHERE CreditRating = 1 OR 
       CreditRating = 2 OR 
       CreditRating = 3 OR 
       CreditRating = 4 OR 
       CreditRating = 5; 

Using NOT the Operator 

What if we want to find all vendors that don’t have a credit rating of 1?  Is there a short cut we can take? 

Of course!  There are several, but here we’ll focus on using the NOT operator.  The NOT operator is used to change a TRUE value to FALSE, and FALSE to TRUE. 

So, when when you’re looking to match credit ratings = 1, you write CreditRating = 1.  This equals TRUE every time a CreditRating is 1 and FALSE when it doesn’t. 

Since the WHERE clause returns rows on TRUE conditions, if we want to get credit ratings not equal to one, we can write NOT CreditRating = 1. 

Here is the full query: 

SELECT AccountNumber
	 , Name
	 , CreditRating
	 , PreferredVendorStatus
	 , ActiveFlag
	 , ModifiedDate
FROM Purchasing.Vendor
WHERE NOT CreditRating = 1; 

Using Comparison Operators – More SQL WHERE examples 

You can also use comparison operators such as Greater Than in your queries. 

Here is a list of the operators you can use and their meanings: 

• > Greater Than 
• >=  Greater Than or Equal To 
• <   Less Than 
• <=  Less Than or Equal To 
• = Equal To
• <>  – Not Equal To 

Going back to the credit rating SQL WHERE example, here is another way to get all credit ratings not equal 1? 

SELECT AccountNumber
	 , Name
	 , CreditRating
	 , PreferredVendorStatus
	 , ActiveFlag
	 , ModifiedDate
FROM Purchasing.Vendor
WHERE CreditRating <> 1;  

Can you think of other ways to get credit ratings not equal to 1?  How about you try writing a query doing so using the greater than operator.

SELECT AccountNumber
	 , Name
	 , CreditRating
	 , PreferredVendorStatus
	 , ActiveFlag
	 , ModifiedDate
FROM Purchasing.Vendor
WHERE CreditRating > 1; 

Additional SQL WHERE Examples Resources 

If you’re looking to learn more about using the SQL WHERE examples, then I would recommend these resources: 

• Good introductory article on the WHERE clause: How to Filter Your Query Results 
• Query Results Using Boolean Logic 
• Looking for a super SQL book? Check out SQL Queries for Mere Mortals.