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.
Let’s see you give one a try. How would you change the SQL to also include vendors with 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.
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:
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?
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.
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.