The WHERE with AND, OR, NOT syntax

A WHERE clause with AND:

1. SELECT column–names
2. FROM table–name
3. WHERE condition1 AND condition2

A WHERE clause with OR:

1. UPDATE table–name
2. SET column–name = value
3. WHERE condition1 OR condition2

A WHERE clause with NOT:

1. DELETE table–name
2. WHERE NOT condition

 

---

 

SQL WHERE with AND, OR, and NOT Examples

Problem: Get customer named Thomas Hardy

1. SELECT Id, FirstName, LastName, City, Country
2. FROM Customer
3. 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

1. SELECT Id, FirstName, LastName, City, Country
2. FROM Customer
3. 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

1. SELECT Id, FirstName, LastName, City, Country
2. FROM Customer
3. 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

1. SELECT Id, OrderDate, CustomerId, TotalAmount
2. FROM [Order]
3. WHERE NOT (TotalAmount >= 50 AND TotalAmount <= 15000)
4. 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