- 4 years ago
- Zaid Bin Khalid
- 2586 Views
-
3
In this session, 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 session, we have 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 the United States, selecting products whose price is lower than 100 dollars and ratings are greater than 4, etc.
The AND Operator.
The AND operator returns TRUE when all conditions of this operator are TRUE. This is also a logical operator that returns True or False.
Example.
In order to filter the results, one might use AND operator along with WHERE clause to create a condition such as UPDATE, DELETE and SELECT statement.
SELECT column1_name, column2_name, columnN_name
FROM table_name
WHERE condition1 AND condition2
Proposed Table.
Suppose we have 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 the employees with a salary greater than 7000 and the department ID equal to 5.
SELECT * FROM employees WHERE salary > 7000 AND dept_id = 5;
Output Results for AND operator.
After executing, 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.
Similar to AND operator the execution of OR operator is also based upon the logical combination of two conditions. However, the resulting output of this operator can only be executed if either of the condition is TRUE.
Example.
Let suppose 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;
Output Results of OR Operator.
This time you will get the result 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 use both operators at a time when you use the complex combination. But remember when you use complex combinations then must use small brackets to define the scope of the condition.
Example.
It can be observed that the following SQL statement will return the salary data of all the employees. Whose values are higher than 5000 as well as return the dept_id value that is equal to 1 or 5.
SELECT * FROM employees WHERE salary > 5000 AND (dept_id = 1 OR dept_id = 5);
Output Results of combined AND & OR Operator.
The resulting output of the above query will provide following executing results.
+--------+--------------+------------+--------+---------+
| 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 |
+--------+--------------+------------+--------+---------+
- 4 years ago
- Zaid Bin Khalid
- 2586 Views
-
3