SQL Where

Selecting Record Based on Condition

In the previous chapter, we learned how to retrieve all data from a table or specific columns. However, in practical scenarios, we often need to work with records that meet specific conditions, such as users belonging to a certain age group or country.

The WHERE clause is crucial in SQL for filtering data in operations like SELECT, UPDATE, and DELETE. It's also used in various other SQL statements as we'll explore in upcoming chapters.

Syntax

The WHERE clause is used with the SELECT statement to fetch records that meet specific conditions. The basic syntax is:

Here, column_list refers to the names of columns like name, age, country, etc., from which you want to retrieve data. If you want all columns, you can use:

Now, let's explore some examples to understand how it works in practice.

Imagine we have a table named employees in our database containing 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 |
+--------+--------------+------------+--------+---------+

Filter Records with WHERE Clause

This SQL query retrieves employees from the employees table where the salary exceeds 7000. The WHERE clause effectively excludes data that doesn't meet this criterion.

SELECT * FROM employees
WHERE salary > 7000;

Upon execution, the result will resemble 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 |
+--------+--------------+------------+--------+---------+

As observed, the output displays employees whose salary exceeds 7000. Similarly, you can retrieve records from specific columns, as shown:

SELECT emp_id, emp_name, hire_date, salary
FROM employees
WHERE salary > 7000;

After running the statement above, the output will resemble this:

+--------+--------------+------------+--------+
| emp_id | emp_name     | hire_date  | salary |
+--------+--------------+------------+--------+
|      3 | Sarah Connor | 2005-10-18 |   8000 |
|      4 | Rick Deckard | 2007-01-03 |   7200 |
+--------+--------------+------------+--------+

This statement retrieves the details of an employee whose employee ID is 2.

SELECT * FROM employees
WHERE emp_id = 2;

This statement will generate the following result:

+--------+--------------+------------+--------+---------+
| emp_id | emp_name     | hire_date  | salary | dept_id |
+--------+--------------+------------+--------+---------+
|      2 | Tony Montana | 2002-07-15 |   6500 |       1 |
+--------+--------------+------------+--------+---------+

This time, we see only one row in the output because each employee's emp_id is unique.

Operators Permitted in WHERE Clause

SQL offers several operators that are usable within the WHERE clause. Below is a summary of the most significant ones: