How to Use Multiple WHERE Conditions in a Single Query

Problem:

You want to apply multiple conditions to filter the data you want to retrieve.

Example 1:

A company has its employee data stored in the table employees with the columns empId, empName, dept, and salary.

Let’s say you want to find all who work in the Finance department and are paid more than 4,000.

Solution:

You have two conditions for the filter: 1) the department is Finance; 2) the salary is more than 4,000.

Let’s see what the solution looks like:

SELECT *
FROM employees
WHERE dept = 'Finance' AND salary > 4000;

And this is the result:

Discussion:

To filter data by multiple conditions in a WHERE clause, use the AND operator to connect the conditions. Here’s what this looks like for two conditions:

WHERE condition1 AND condition2

In our example, condition1 is dept = 'Finance' and condition2 is salary > 4000.

Using the AND operator, you may chain as many conditions as you want. For example, to find employees who work in the HR department and earn more than 4,000 but less than 6,000, write a query like this:

SELECT *
FROM employees
WHERE dept = 'HR' AND salary > 4000 AND salary < 6000;

Example 2:

Let’s take the same table as the above but change the conditions. This time, you want to select employees who work in either the Finance department or the Sales department.

Solution:

In this example, you have two conditions, but only one of them has to apply: 1) the department is Finance; 2) the department is Sales.

Let’s see what the solution looks like:

SELECT *
FROM employees
WHERE dept = 'Finance' OR dept = 'Sales';

And this is what you get:

Discussion:

The operator OR stands between conditions and may be used to chain multiple conditions:

WHERE condition1 OR condition2 OR condition3

In our example, we have two conditions. The first condition is dept = 'Finance', and the second condition is dept = 'Sales'. The difference between the AND operator and the OR operator is that the OR operator requires any of the conditions to be satisfied for the row to be included in the result, whereas the AND operator requires all conditions to be satisfied for the row to be included in the result.

The AND and OR operators may be combined to form complex conditions like this:

WHERE condition1 OR (condition2 AND condition3)

For example, to find employees who earn between 4000 and 6000 or who work in the Finance department, write a query like this:

SELECT *
FROM employees
WHERE dept = 'Finance' OR (salary > 4000 AND salary < 4000);