SQL AND & OR Operators

In this tutorial you will learn how to use the AND & OR operators with the WHERE clause to filter records based on more than one condition.

Selecting Record Based on Condition

In the previous chapter we've learned how to fetch records from a table using a single condition with the WHERE clause. But sometimes you need to filter records based on multiple conditions like selecting users whose ages are greater than 30 and country is United States, selecting products whose price is lower than 100 dollar and ratings is greater than 4, etc.

The AND Operator

The AND operator is a logical operator that combines two conditions and returns TRUE only if both condition evaluate to TRUE . The AND operator is often used in the WHERE clause of the SELECT, UPDATE, DELETE statement to form conditions to filter the result set.

SELECT column1_name, column2_name, columnN_name
FROM table_name
WHERE condition1 AND condition2;

Let's check out some examples that demonstrate how it actually works.

Suppose we've a table called employees in our database with the following records:

+--------+--------------+------------+--------+---------+
| emp_id | emp_name     | hire_date  | salary | dept_id |
+--------+--------------+------------+--------+---------+
|      1 | Ethan Hunt   | 2001-05-01 |   5000 |       4 |
|      2 | Tony Montana | 2002-07-15 |   6500 |       1 |
|      3 | Sarah Connor | 2005-10-18 |   8000 |       5 |
|      4 | Rick Deckard | 2007-01-03 |   7200 |       3 |
|      5 | Martin Blank | 2008-06-24 |   5600 |    NULL |
+--------+--------------+------------+--------+---------+

Using WHERE Clause with AND Operator

The following SQL statement will return only those employees from the employees table whose salary is greater than 7000 and the dept_id is equal to 5.

SELECT * FROM employees
WHERE salary > 7000 AND dept_id = 5;

After execution, you will get the output something like this:

+--------+--------------+------------+--------+---------+
| emp_id | emp_name     | hire_date  | salary | dept_id |
+--------+--------------+------------+--------+---------+
|      3 | Sarah Connor | 2005-10-18 |   8000 |       5 |
+--------+--------------+------------+--------+---------+

The OR Operator

Similarly, the OR operator is also a logical operator that combines two conditions, but it returns TRUE when either of the conditions is TRUE.

The following SQL statement will return all the employees from the employees table whose salary is either greater than 7000 or the dept_id is equal to 5.

SELECT * FROM employees
WHERE salary > 7000 OR dept_id = 5;

This time you will get the output something like this:

+--------+--------------+------------+--------+---------+
| emp_id | emp_name     | hire_date  | salary | dept_id |
+--------+--------------+------------+--------+---------+
|      3 | Sarah Connor | 2005-10-18 |   8000 |       5 |
|      4 | Rick Deckard | 2007-01-03 |   7200 |       3 |
+--------+--------------+------------+--------+---------+

Combining AND & OR Operator

You can also combine AND and OR to create complex conditional expressions.

The following SQL statement will return all the employees whose salary is greater than 5000 and the dept_id is either equal to 1 or 5.

SELECT * FROM employees
WHERE salary > 5000 AND (dept_id = 1 OR dept_id = 5);

After executing the above query, you will get the output something like this:

+--------+--------------+------------+--------+---------+
| emp_id | emp_name     | hire_date  | salary | dept_id |
+--------+--------------+------------+--------+---------+
|      2 | Tony Montana | 2002-07-15 |   6500 |       1 |
|      3 | Sarah Connor | 2005-10-18 |   8000 |       5 |
+--------+--------------+------------+--------+---------+