SQL AND & OR Operators

The AND & OR operators are used with the WHERE clause to filter records based on more than one condition.

The AND operator shows a record if all the conditions separated by AND are TRUE.

The OR operator shows a record if any of the conditions separated by OR is TRUE.

The AND & OR operators are called the conjunctive operators.

Selecting Record Based on Multiple Condition

In the previous chapter, we see how to select records from a table using a single condition with the WHERE clause. But in the real-life scenario, we often need to filter records based on multiple conditions like selecting customers that live in the United States and whose ages are greater than 34.

The AND Operator

The AND operator is a logical operator used to combine two conditions and returns TRUE only if both conditions are TRUE.

The AND operator is used to set multiple conditions with the WHERE clause of the SELECT, UPDATE, DELETE statement to form conditions to filter the result set.

AND Syntax

The basic syntax for the AND operator is as the following:

SELECT column1_name, column2_name, ...columnN_name
FROM table_name
WHERE [condition1] AND [condition2] ...AND [conditionN];

You can combine N number of conditions with the AND operator. All conditions separated by the AND operator must be TRUE for the action to be taken by the SQL statement.

The OR operator

The OR operator is also a logical operator used to combine two conditions and returns TRUE if any of the conditions is TRUE.

The difference between AND & OR is their behaviour. The AND operator needs to satisfy all the conditions, while the OR operator needs to satisfy at least one condition.

OR Syntax

The basic syntax for the OR operator is as the following:

SELECT column1_name, column2_name, ...columnN_name
FROM table_name
WHERE [condition1] OR  [condition2] ...OR [conditionN]

You can combine N number of conditions with the OR operator. At least ONE of the conditions separated by the OR operator must be TRUE for the action to be taken by the SQL statement.

Demo Table

Let us suppose that we have a table called "employee" in our database with the following records:

Example Using WHERE Clause with AND Operator

The following SQL statement will return all employees that work in the "Development" department and salary are greater than 8000.

SELECT * FROM employee 
WHERE dept_name = "Development" AND salary > 8000

The output of the above SQL statement will be as the following:

+--------+-------------+-----------+------------+------------+-----------+------------------+
| emp_no | first_name  | last_name | birth_date | hire_date  | salary    | dept_name        |
+--------+-------------+-----------+------------+------------+-----------+------------------+
| 4      | Kayoko      | Valtorta  | 1995-09-07 | 2019-09-08 | 8600      | Development      |
+--------+-------------+-----------+------------+------------+-----------+------------------+

Example Using WHERE Clause with OR Operator

The following SQL statement will return all employees either work in the "Finance" department or salary are greater than 8000.

SELECT * FROM employee
WHERE dept_name = "Finance" OR salary > 8000

This time the output will be as the following:

+--------+-------------+-----------+------------+------------+-----------+------------------+
| emp_no | first_name  | last_name | birth_date | hire_date  | salary    | dept_name        |
+--------+-------------+-----------+------------+------------+-----------+------------------+
| 3      | Maria       | Rodriguez | 1994-10-25 | 2019-01-23 | 8500      | Finance          |
| 4      | Kayoko      | Valtorta  | 1995-09-07 | 2019-09-08 | 8600      | Development      |
| 5      | Babette     | Lamba     | 1996-03-15 | 2012-06-06 | 6000      | Finance          |
+--------+-------------+-----------+------------+------------+-----------+------------------+

Combining AND & OR Operators

You can also combine the AND & OR operators.

The following SQL statement will select all the employees from the employee table whose salary is greater than 7000 and work in the "Finance" or "Human Resources" department.

SELECT * FROM 
FROM employee
WHERE salary > 7000 AND (dept_name = 'Finance' OR dept_name = 'Human Resources');

The above SQL statement will output the following result set:

+--------+-------------+-----------+------------+------------+-----------+------------------+
| emp_no | first_name  | last_name | birth_date | hire_date  | salary    | dept_name        |
+--------+-------------+-----------+------------+------------+-----------+------------------+
| 3      | Maria       | Rodriguez | 1994-10-25 | 2019-01-23 | 8500      | Finance          |
| 7      | Brendon     | Lenart    | 1998-01-17 | 2009-12-12 | 7500      | Human Resources  |
+--------+-------------+-----------+------------+------------+-----------+------------------+

Note: When combining The AND & OR operator, it is important to use parentheses, so the database system will know in which order to evaluate each condition.