SQL AND & OR

Selecting Record Based on Condition

In our previous lesson, we learned how to retrieve data from a table using a single condition with the WHERE clause. However, in real-world scenarios, you often need to filter records based on multiple conditions, such as selecting users over 30 years old from the United States, or products priced under $100 with a rating above 4.

The AND Operator

The AND operator is a logical operator that combines two conditions and returns TRUE only if both conditions evaluate to TRUE. It's commonly used in the WHERE clause of SELECT, UPDATE, and DELETE statements to create conditions for filtering results.

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

Let's explore some examples to see how it works in practice.

Suppose we have a table named 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 next SQL query will retrieve employees from the employees table where their salary exceeds 7000 and their dept_id matches 5.

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

After running the query, the result will look similar to 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 a logical operator that combines two conditions, returning TRUE if either condition is met.

The next SQL statement retrieves all employees from the employees table whose salary exceeds 7000 or whose dept_id equals 5.

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

This time, the result will appear similar to 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 use both AND and OR operators together to form complex conditional expressions.

The next SQL statement retrieves employees whose salary exceeds 5000 and belong to either department 1 or department 5.

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

After running the query above, the output will resemble 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 |
+--------+--------------+------------+--------+---------+