SQL WHERE with AND, OR, NOT

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

#

List all suppliers in Paris, France.

SELECT CompanyName, ContactName, City, Country, Phone, Fax
  FROM Supplier
 WHERE Country = 'France' AND City = 'Paris'
Result: 1 record
CompanyName ContactName City Country Phone
Aux joyeux ecclésiastiques Guylène Nodier Paris France (1) 03.83.00.68

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

WHERE with AND

CUSTOMER
Id
FirstName
LastName
City
Country
Phone
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

WHERE with OR

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

WHERE with NOT

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

WHERE with NOT, AND

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

You may also like



Guides


vsn 3.1