1.4.12.7 AND & OR

AND and OR join two or more conditions in a WHERE clause.

The AND operator displays a row if ALL conditions listed are true. The OR operator displays a row if ANY of the conditions listed are true.

Original Table (used in the examples)

+----------+-----------+--------+-------------+
| LastName | FirstName | Number | DateOfBirth |
+----------+-----------+--------+-------------+
| 'Smith'  | 'John'    | 1      | 1980-01-01  |
| 'Doe'    | 'John'    | 1      | 1980-01-01  |
| 'Blair'  | 'James'   | 8      | 1953-05-06  |
+----------+-----------+--------+-------------+

Use AND to display each person with the first name equal to John, and the last name equal to Smith:

SELECT * FROM Person WHERE FirstName='John' AND LastName='Smith'

Result Set

+----------+-----------+--------+-------------+
| LastName | FirstName | Number | DateOfBirth |
+----------+-----------+--------+-------------+
| 'Smith'  | 'John'    | 1      | 1980-01-01  |
+----------+-----------+--------+-------------+

Use OR to display each person with the first name equal to James, or the last name equal to Smith:

SELECT * FROM Person WHERE FirstName='James' OR LastName='Smith'

Result Set

+----------+-----------+--------+-------------+
| LastName | FirstName | Number | DateOfBirth |
+----------+-----------+--------+-------------+
| 'Smith'  | 'John'    | 1      | 1980-01-01  |
| 'Blair'  | 'James'   | 8      | 1953-05-06  |
+----------+-----------+--------+-------------+

Example

You can also combine AND and OR use parentheses to form complex expressions:

SELECT * FROM Person WHERE (FirstName='James' AND LastName='Smith') OR LastName='Blair'

Result Set

+----------+-----------+--------+-------------+
| LastName | FirstName | Number | DateOfBirth |
+----------+-----------+--------+-------------+
| 'Blair'  | 'James'   | 8      | 1953-05-06  |
+----------+-----------+--------+-------------+