SQL WHERE clause

In this tutorial, you will learn how to select specific records from a table using SQL.

Selecting records based on conditions

In the previous chapter, we learned how to retrieve all records from a table or table column. However, in the real world, we usually only need to select, update, or delete records that meet certain conditions, such as those belonging to a certain age group or country/users from the region, etc.

WHERE clause is used withSELECT,UPDATEandDELETE. However, you will see in the next chapters how to use this clause with other statements.

Syntax

The WHERE clause is used with the SELECT statement to extract only those records that meet the specified conditions. The basic syntax can be given as follows:

SELECT　column_list　FROM　table_name　WHERE　condition;

Here,column_listis the column of the database table whose value you want to retrieve/fieldName，for examplename,age,countryetc. However, if you want to get the values of all available columns in the table, you can use the following syntax:

SELECT　*　FROM　table_name　WHERE　condition;

Now, let's look at some examples to demonstrate how it actually works.

Suppose we have a database table namedemployeeswhich 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　|
+--------+--------------+------------+--------+---------+

using the WHERE clause to filter records

The following SQL statement will retrieve records fromemployeesthat have a salary greater than7000 of all employees. The WHERE clause simply filters out the unnecessary data.

SELECT　*　FROM　employees
WHERE　salary　>　7000;

After execution, the output will be as follows:

+--------+--------------+------------+--------+---------+
|　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 you can see, the output only contains salaries greater than7000 of those employees. Similarly, you can retrieve records from specific columns as shown below:

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

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

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

The following statement will retrieve the record of an employee with2of an employee's record.

SELECT　*　FROM　employees
WHERE　emp_id　=　2;

This statement will produce the following output:

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

This time, we only get one line in the output becauseemp_idEach employee is unique.

Operators allowed in the WHERE clause

SQL supports many operators that can be used in the WHERE clause. The following table summarizes the most important operators.