SQL And
The SQL AND Operator
There may be one or more AND operators in the WHERE clause.
When filtering data based on several criteria, the AND operator is utilized. For example, if you want to retrieve all Spanish customers whose first letter begins with “G,” do the following:
Example
Select all customers from Spain that starts with the letter ‘G’:
SELECT *
FROM Customers
WHERE Country = 'Spain' AND CustomerName LIKE 'G%';
Syntax
SELECT column1, column2, …
FROM table_name
WHERE condition1 AND condition2 AND condition3 …;
AND vs OR
The AND operator displays a record if all the conditions are TRUE.
The OR operator displays a record if any of the conditions are TRUE.
Demo Database
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 1 |
Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
| 3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | 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 |
All Conditions Must Be True
The SQL query that follows picks every field from Customers where PostalCode is greater than 12000 AND City is “Berlin” AND Country is “Germany”:
Example
SELECT * FROM Customers
WHERE Country = 'Germany'
AND City = 'Berlin'
AND PostalCode > 12000;
Combining AND and OR
It is possible to combine the operators AND and OR.
The clients from Spain that begin with a “G” or a “R” are all chosen by the SQL query that follows.
For the desired outcome, make sure you utilize parentheses.
Example
Select all Spanish customers that starts with either “G” or “R”:
SELECT * FROM Customers
WHERE Country = 'Spain' AND (CustomerName LIKE 'G%' OR CustomerName LIKE 'R%');
When the select statement is run without parenthesis, all Spanish customers with a “G” as well as all customers with a “R” as of any nation value will be returned:
Example
Select all customers that either:
are from Spain and starts with either “G”, or
starts with the letter “R”:
SELECT * FROM Customers
WHERE Country = 'Spain' AND CustomerName LIKE 'G%' OR CustomerName LIKE 'R%';
