SQL WHERE AND, OR, NOT Clause

The WHERE with AND, OR, NOT syntax

A WHERE clause with AND:

  1. SELECT columnnames
  2. FROM tablename
  3. WHERE condition1 AND condition2

A WHERE clause with OR:

  1. UPDATE tablename
  2. SET columnname = value
  3. WHERE condition1 OR condition2

A WHERE clause with NOT:

  1. DELETE tablename
  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

댓글 남기기