Login Create account  

SQL WHERE with AND, OR, and NOT

AND, OR, and NOT Explained

WHERE conditions can be combined with AND, OR, and NOT.

These logical conditions always return true or false.

A WHERE with AND requires that two conditions are true.

A WHERE with OR requires that one of two conditions is true.

A WHERE with NOT negates the specified condition.

Example

#

Problem: List all suppliers in Paris, France.

copy 
SELECT * 
  FROM Supplier
 WHERE Country = 'France' AND City = 'Paris'

Syntax

WHERE clause with AND. 

SELECT column-names
  FROM table-name
 WHERE condition1 AND condition2

WHERE clause with OR.

UPDATE table-name
   SET column-name = value
 WHERE condition1 OR condition2

WHERE clause with NOT.

DELETE table-name
 WHERE NOT condition

More Examples

SQL WHERE with AND

CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Problem: Get the customer named Thomas Hardy.
copy 
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

SQL WHERE with OR

CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Problem: List all customers from Spain or France.
copy 
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

SQL WHERE with NOT

CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Problem: List all customers that are not from the USA.
copy 
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

SQL WHERE with NOT, AND

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
Problem: List all orders that are not between $50 and $15000.
copy 
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

You may also like



Feedback
Guides


On this page