WHERE, AND/OR/NOT, NULL, LIKE, IN, and BETWEEN
Used to filter records given specified conditions in multiple statements, including:
SELECTUPDATEDELETE
Operators are needed to specify the conditions:
| Operator | Description |
|---|---|
| = | Equal |
| <> or != | Not equal |
| > | Greater than |
| < | Less than |
| >= | Greater than or equal |
| <= | Less than or equal |
| IS NULL | Display a record containing no value in certain field(s) |
| IS NOT NULL | Display a record that must contain a value in certain field(s) |
| LIKE ‘%’ | Wildcard for 0, 1, or multiple characters |
| LIKE ’_’ | Wildcard for a single character |
| BETWEEN | Between a certain range |
| AND | Displays a record if ALL the conditions are TRUE |
| OR | Displays a record if ANY of the conditions is TRUE |
| IN | To specify multiple possible values for a column |
Syntax
Select records if the specified condition is true:
Select records if all of the specified conditions are true:
SELECT <column_name1>, <column_name2>, ...
FROM <table_name>
WHERE <condition1> AND <condition2> AND <condition3> ... ;Select records if any of the specified conditions are true:
SELECT <column_name1>, <column_name2>, ...
FROM <table_name>
WHERE <condition1> OR <condition2> OR <condition3> ... ;OR
Combine AND and OR in a single query:
SELECT <column_name1>, <column_name2>, ...
FROM <table_name>
WHERE <condition1> AND (<condition2> OR <condition3>) ... ;Examples
Basics
Select all customers from Mexico:
Note: text values must be enclosed in single/double quotes.
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 5021 | Mexico |
| 3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 5023 | Mexico |
| 13 | Centro comercial Moctezuma | Francisco Chang | Sierras de Granada 9993 | México D.F. | 5022 | Mexico |
| 58 | Pericles Comidas clásicas | Guillermo Fernández | Calle Dr. Jorge Cash 321 | México D.F. | 5033 | Mexico |
| 80 | Tortuga Restaurante | Miguel Angel Paolino | Avda. Azteca 123 | México D.F. | 5033 | Mexico |
Select all records where the customer is not in Berlin:
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 5021 | Mexico |
| 3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 5023 | Mexico |
| 4 | Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
| 5 | Berglunds snabbköp | Christina Berglund | Berguvsvägen 8 | Luleå | S-958 22 | Sweden |
| 6 | Blauer See Delikatessen | Hanna Moos | Forsterstr. 57 | Mannheim | 68306 | Germany |
| 7 | Blondel père et fils | Frédérique Citeaux | 24, place Kléber | Strasbourg | 67000 | France |
| 8 | Bólido Comidas preparadas | Martín Sommer | C/ Araquil, 67 | Madrid | 28023 | Spain |
| 9 | Bon app’ | Laurence Lebihans | 12, rue des Bouchers | Marseille | 13008 | France |
| 10 | Bottom-Dollar Marketse | Elizabeth Lincoln | 23 Tsawassen Blvd. | Tsawassen | T2F 8M4 | Canada |
| 11 | B’s Beverages | Victoria Ashworth | Fauntleroy Circus | London | EC2 5NT | UK |
AND
Select all records of customers in Berlin and has the postal code 12209:
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
OR / IN
Select all records of customers in Berlin or London:
Option 1
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
| 4 | Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
| 11 | B’s Beverages | Victoria Ashworth | Fauntleroy Circus | London | EC2 5NT | UK |
| 16 | Consolidated Holdings | Elizabeth Brown | Berkeley Gardens 12 Brewery | London | WX1 6LT | UK |
| 19 | Eastern Connection | Ann Devon | 35 King George | London | WX3 6FW | UK |
| 53 | North/South | Simon Crowther | South House 300 Queensbridge | London | SW7 1RZ | UK |
| 72 | Seven Seas Imports | Hari Kumar | 90 Wadhurst Rd. | London | OX15 4NB | UK |
Option 2
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
| 4 | Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
| 11 | B’s Beverages | Victoria Ashworth | Fauntleroy Circus | London | EC2 5NT | UK |
| 16 | Consolidated Holdings | Elizabeth Brown | Berkeley Gardens 12 Brewery | London | WX1 6LT | UK |
| 19 | Eastern Connection | Ann Devon | 35 King George | London | WX3 6FW | UK |
| 53 | North/South | Simon Crowther | South House 300 Queensbridge | London | SW7 1RZ | UK |
| 72 | Seven Seas Imports | Hari Kumar | 90 Wadhurst Rd. | London | OX15 4NB | UK |
Select all customers who are from the same countries as the suppliers:
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
| 4 | Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
| 5 | Berglunds snabbköp | Christina Berglund | Berguvsvägen 8 | Luleå | S-958 22 | Sweden |
| 6 | Blauer See Delikatessen | Hanna Moos | Forsterstr. 57 | Mannheim | 68306 | Germany |
| 7 | Blondel père et fils | Frédérique Citeaux | 24, place Kléber | Strasbourg | 67000 | France |
| 8 | Bólido Comidas preparadas | Martín Sommer | C/ Araquil, 67 | Madrid | 28023 | Spain |
| 9 | Bon app’ | Laurence Lebihans | 12, rue des Bouchers | Marseille | 13008 | France |
| 10 | Bottom-Dollar Marketse | Elizabeth Lincoln | 23 Tsawassen Blvd. | Tsawassen | T2F 8M4 | Canada |
| 11 | B’s Beverages | Victoria Ashworth | Fauntleroy Circus | London | EC2 5NT | UK |
| 15 | Comércio Mineiro | Pedro Afonso | Av. dos Lusíadas, 23 | São Paulo | 05432-043 | Brazil |
BETWEEN
Select all products priced between $10-20:
| ProductID | ProductName | SupplierID | CategoryID | Unit | Price |
|---|---|---|---|---|---|
| 1 | Chais | 1 | 1 | 10 boxes x 20 bags | 18.00 |
| 2 | Chang | 1 | 1 | 24 - 12 oz bottles | 19.00 |
| 3 | Aniseed Syrup | 1 | 2 | 12 - 550 ml bottles | 10.00 |
| 15 | Genen Shouyu | 6 | 2 | 24 - 250 ml bottles | 15.50 |
| 16 | Pavlova | 7 | 3 | 32 - 500 g boxes | 17.45 |
| 21 | Sir Rodney’s Scones | 8 | 3 | 24 pkgs. x 4 pieces | 10.00 |
| 25 | NuNuCa Nuß-Nougat-Creme | 11 | 3 | 20 - 450 g glasses | 14.00 |
| 31 | Gorgonzola Telino | 14 | 4 | 12 - 100 g pkgs | 12.50 |
| 34 | Sasquatch Ale | 16 | 1 | 24 - 12 oz bottles | 14.00 |
| 35 | Steeleye Stout | 16 | 1 | 24 - 12 oz bottles | 18.00 |
Wildcard
Identify all the countries whose name start with “S”:
| Country |
|---|
| Sweden |
| Spain |
| Switzerland |
References
Links to tutorials:
- SQL Where: https://www.w3schools.com/sql/sql_where.asp
- SQL AND, OR, NOT Operators: https://www.w3schools.com/sql/sql_and_or.asp
- SQL NULL Values: https://www.w3schools.com/sql/sql_null_values.asp
- SQL LIKE: https://www.w3schools.com/sql/sql_like.asp
- SQL Wildcards: https://www.w3schools.com/sql/sql_wildcards.asp
- SQL IN: https://www.w3schools.com/sql/sql_in.asp
- SQL BETWEEN: https://www.w3schools.com/sql/sql_between.asp