WHERE, AND/OR/NOT, NULL, LIKE, IN, and BETWEEN

Syntax
Examples
- Basics
- AND
- OR / IN
- BETWEEN
- Wildcard
References

Used to filter records given specified conditions in multiple statements, including:

SELECT
UPDATE
DELETE

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 <column_name1>, <column_name2>, ...
FROM <table_name>
WHERE <condition>;

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> ... ;

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

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.

SELECT * FROM Customers
WHERE Country = "Mexico";

5 records
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:

SELECT * FROM Customers
WHERE City <> 'Berlin';

Displaying records 1 - 10
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:

SELECT * FROM Customers
WHERE City = "Berlin" AND PostalCode = "12209";

1 records
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

SELECT * FROM Customers
WHERE City = "Berlin" OR City = "London";

7 records
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

SELECT * FROM Customers
WHERE City IN ("Berlin", "London");

7 records
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:

SELECT * FROM Customers
WHERE Country IN (SELECT Country FROM Suppliers);

Displaying records 1 - 10
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:

SELECT * FROM prices
WHERE Price BETWEEN 10 AND 20;

Displaying records 1 - 10
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”:

SELECT DISTINCT(Country) FROM  customers
WHERE Country LIKE "S%";

3 records
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

WHERE, AND/OR/NOT, NULL, LIKE, IN, and BETWEEN

WHERE, AND/OR/NOT, NULL, LIKE, IN, and BETWEEN

Syntax

Examples

Basics

AND

OR / IN

BETWEEN

Wildcard

References

`AND`

`OR` / `IN`

`BETWEEN`