SQL WHERE AND, OR, NOT Clause

  • 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.
  • A WHERE clause with NOT negates the specified condition.
The definitive guide
for data professionals


Order today!
 See 2 min video



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




SQL WHERE with AND, OR, and NOT Examples



Problem: Get customer named Thomas Hardy

SELECT Id, FirstName, LastName, City, Country
  FROM Customer
 WHERE FirstName = 'Thomas' AND LastName = 'Hardy'


Results: 1 record.

Id FirstName LastName City Country
4 Thomas Hardy London UK




Problem: List all customers from Spain or France
SELECT Id, FirstName, LastName, City, Country
  FROM Customer
 WHERE Country = 'Spain' OR Country = 'France'


Results: 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




Problem: List all customers that are not from the USA
SELECT Id, FirstName, LastName, City, Country
  FROM Customer
 WHERE NOT Country = 'USA'


Results: 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




Problem: List all orders that not between $50 and $15000
SELECT Id, OrderDate, CustomerId, TotalAmount
  FROM [Order]
 WHERE NOT (TotalAmount >= 50 AND TotalAmount <= 15000)
 ORDER BY TotalAmount DESC


Results: 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