How to Use Multiple WHERE Conditions in a Single Query

Table of Contents

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`.

empIdempNamedeptsalary
1Anthony VillaSales3400.00
2Megan WhitneyHR4000.00
3Clayton CruzFinance3500.00
4Ahmed LiFinance4150.00
5Anna NewtonManager6450.00
6Filip SchaeferHR2850.00

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:

empIdempNamedeptsalary
4Ahmed LiFinance4150.00

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:

empIdempNamedeptsalary
1Anthony VillaSales3400.00
3Clayton CruzFinance3500.00
4Ahmed LiFinance4150.00

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);
```