SQL WHERE AND, OR, NOT Clause
How do I write more complex conditional logic in SQL?
WHERE conditions can be combined with AND, OR, and NOT.
A WHERE clause with AND requires that two conditions are true.
A WHERE clause with OR requires that one of two conditions is true.
And a WHERE clause with NOT negates the specified condition.
The WHERE with AND, OR, NOT syntax
A WHERE clause with AND:
SELECT column-names FROM table-name WHERE condition1 AND condition2
A WHERE clause with OR:
UPDATE table-name SET column-name = value WHERE condition1 OR condition2
A WHERE clause with NOT:
DELETE table-name WHERE NOT condition
CUSTOMER |
---|
Id |
FirstName |
LastName |
City |
Country |
Phone |
SQL WHERE with
AND, OR, and NOT Examples
Problem: Get the customer named Thomas Hardy.
SELECT Id, FirstName, LastName, City, Country FROM Customer WHERE FirstName = 'Thomas' AND LastName = 'Hardy'
Result: 1 record.
Id | FirstName | LastName | City | Country |
---|---|---|---|---|
4 | Thomas | Hardy | London | UK |
CUSTOMER |
---|
Id |
FirstName |
LastName |
City |
Country |
Phone |
Problem: List all customers from Spain or France
SELECT Id, FirstName, LastName, City, Country FROM Customer WHERE Country = 'Spain' OR Country = 'France'
Result: 16 records.
Id | FirstName | LastName | City | Country |
---|---|---|---|---|
7 | Frédérique | Citeaux | Strasbourg | France |
8 | Martín | Sommer | Madrid | Spain |
9 | Laurence | Lebihan | Marseille | France |
18 | Janine | Labrune | Nantes | France |
22 | Diego | Roel | Madrid | Spain |
23 | Martine | Rancé | Lille | France |
![]() |
CUSTOMER |
---|
Id |
FirstName |
LastName |
City |
Country |
Phone |
Problem: List all customers that are not from the USA
SELECT Id, FirstName, LastName, City, Country FROM Customer WHERE NOT Country = 'USA'
Result: 78 records.
Id | FirstName | LastName | City | Country |
---|---|---|---|---|
1 | Maria | Anders | Berlin | Germany |
2 | Ana | Trujillo | México D.F. | Mexico |
3 | Antonio | Moreno | México D.F. | Mexico |
4 | Thomas | Hardy | London | UK |
5 | Christina | Berglund | Luleå | Sweden |
6 | Hanna | Moos | Mannheim | Germany |
7 | Frédérique | Citeaux | Strasbourg | France |
![]() |
ORDER |
---|
Id |
OrderDate |
OrderNumber |
CustomerId |
TotalAmount |
Problem: List all orders that are not between $50 and $15000
SELECT Id, OrderDate, CustomerId, TotalAmount FROM [Order] WHERE NOT (TotalAmount >= 50 AND TotalAmount <= 15000) ORDER BY TotalAmount DESC
Result: 16 records.
Id | OrderDate | CustomerId | TotalAmount |
---|---|---|---|
618 | 2/2/2014 12:00:00 AM | 63 | 17250.00 |
783 | 4/17/2014 12:00:00 AM | 71 | 16321.90 |
734 | 3/27/2014 12:00:00 AM | 34 | 15810.00 |
175 | 1/22/2013 12:00:00 AM | 27 | 49.80 |
24 | 8/1/2012 12:00:00 AM | 75 | 48.00 |
![]() |