SQL AND & OR operators

In this tutorial, you will learn how to use AND & OR operators in the clause to filter records based on multiple conditions.

Select records based on conditions

In the previous chapter, we learned how to retrieve records from the table using a single condition with the WHERE clause. But sometimes you need to filter records based on multiple conditions, such as selecting records where the age is greater than30 years and the country/users in the United States, select products with a price lower than100 dollars and the rating is greater than4products, etc.

AND operator

The AND operator is a logical operator that combines two conditions and returns TRUE only when both conditions are TRUE. It is commonly used inSELECT,UPDATE,DELETEof the statementWHEREThe AND operator is used in the clause to form conditions to filter the result set.

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

Let's look at some examples to demonstrate how it actually works.

Suppose we have a database with a nameemployeesThe table, which contains 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 |
+--------+--------------+------------+--------+---------+

WHERE clause is used with the AND operator

The following SQL statement will retrieve only fromemployeesThe table returns salaries (salary) greater than7000 anddept_idEqual to5those employees.

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

After execution, you will get the following output:

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

OR operator

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

The following SQL statement will retrieve fromemployeesThe table returns salaries (salary) greater than7000 ordept_idEqual to5All employees.

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

This is the output you will get:

+--------+--------------+------------+--------+---------+
|　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　|
+--------+--------------+------------+--------+---------+

Using AND and OR operators simultaneously

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

The following SQL statement will return salaries greater than5000, anddept_idEqual to1Or5All employees.

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

After executing the above query, you will get the following output:

+--------+--------------+------------+--------+---------+
|　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　|
+--------+--------------+------------+--------+---------+