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


Stay Inspired!
Join other developers and designers who have already signed up for our mailing list.
Terms     Privacy     Licensing       EULA       Sitemap      
© Data & Object Factory, LLC.
Made with    in Austin, Texas