SQL AND and OR operators

In this lesson, you’ll learn how to use AND and OR operators to filter records based on more than one condition.

Using the AND Operator

To filter by more than one column, you use the AND operator to add conditions to your WHERE clause. We will use the Customer table to demonstrate how to fetch specific data from database table with the AND operator:

+----+------------+-------------------+-----------+-------+-------+
| id | name       | address           | city      | state | zip   |
+----+------------+-------------------+-----------+-------+-------+
|  1 | Bill Smith | 123 Main Street   | Hope      | CA    | 98765 |
|  2 | Mary Smith | 123 Dorian Street | Harmony   | AZ    | 98765 |
|  3 | Bob Smith  | 123 Laugh Street  | Hope      | CA    | 98765 |
|  4 | Chang Chao | 123 Dorian Streat | Hong Kong | CN    | 98765 |
|  5 | John Smith | 123 Winges road   | Toronto   | CA    | 98765 |
+----+------------+-------------------+-----------+-------+-------+

We’ll start with a basic syntax of the AND operator with WHERE clause as folows:

SELECT * FROM customer WHERE city=’Hope’ AND state=’CA’;

This would produce the following result:

+----+------------+------------------+------+-------+-------+
| id | name       | address          | city | state | zip   |
+----+------------+------------------+------+-------+-------+
|  1 | Bill Smith | 123 Main Street  | Hope | CA    | 98765 |
|  3 | Bob Smith  | 123 Laugh Street | Hope | CA    | 98765 |
+----+------------+------------------+------+-------+-------+

Using the OR Operator

The OR operator instructs the DBMS software to retrieve rows that match either condition. Here is an example:

SELECT * FROM customer WHERE city=’Hope’ OR state=’CA’;

This would produce the following result:

+----+------------+------------------+---------+-------+-------+
| id | name       | address          | city    | state | zip   |
+----+------------+------------------+---------+-------+-------+
|  1 | Bill Smith | 123 Main Street  | Hope    | CA    | 98765 |
|  3 | Bob Smith  | 123 Laugh Street | Hope    | CA    | 98765 |
|  5 | John Smith | 123 Winges road  | Toronto | CA    | 98765 |
+----+------------+------------------+---------+-------+-------+