SQL WHERE Clause

The SQL WHERE clause is used to filter records and apply conditions in a SELECT, UPDATE, or DELETE statement.

Generally, in a real-world scenario, we need to select, update, delete only records that fulfill certain criteria like customers who belong to a certain country, age or group, etc.

Syntax

The WHERE clause is used with the SELECT statement to extract only records that fulfill specified conditions.

The basic syntax of the WHERE clause is:

SELECT clumn1_name, column2_name, columnN_name 
FROM table_name
WHERE conditions; 

To fetch all the records of all the columns available in a table, use the following syntax:

SELECT * 
FROM table_name 
WHERE conditions;

Filter Records with WHERE Clause

Let us suppose that we have a table called "employee" in our database with the following records:

The following SQL statement will filter the employee table to have just employees that work in the "Development" department.

SELECT * FROM employee
WHERE dept_name = 'Development';

After the execution, the output will be as the following:

+--------+------------+-------------+-----------+------------+------------------+
| emp_no | birth_date | first_name  | last_name | hire_date  | dept_name        |
+--------+------------+-------------+-----------+------------+------------------+
| 2      | 1992-08-13 | Yuichiro    | Swick     | 2018-10-08 | Development      |
| 3      | 1993-12-25 | Chikara     | Rissland  | 2019-01-23 | Development      |
| 4      | 1993-08-07 | Kayoko      | Valtorta  | 2019-09-08 | Development      |
+--------+------------+-------------+-----------+------------+------------------+

In the above, we can see that the output contains only those employees who work in the "Development" department.

If you want to fetch records from specific columns, run the following SQL statement:

SELECT emp_no, first_name, last_name, dept_name
FROM employee
WHERE dept_name = 'Development';

The output of the above statement will look like this:

+--------+-------------+-----------+------------------+
| emp_no | first_name  | last_name | dept_name        |
+--------+-------------+-----------+------------------+
| 2      | Yuichiro    | Swick     | Development      |
| 3      | Chikara     | Rissland  | Development      |
| 4      | Kayoko      | Valtorta  | Development      |
+--------+-------------+-----------+------------------+

The following SQL statement will select the records on an employee whose employee id is 5.

+--------+------------+-------------+-----------+------------+------------------+
| emp_no | birth_date | first_name  | last_name | hire_date  | dept_name        |
+--------+------------+-------------+-----------+------------+------------------+
| 5      | 1995-02-15 | Babette     | Lamba     | 2012-06-06 | Finance          |
+--------+------------+-------------+-----------+------------+------------------+

Text Fields vs. Numeric Fields

SQL requires the use of single quotes around text fields in SQL statements. Most database systems also allow double-quotes for text fields, but it is highly recommended to use single quotes.

On the other hand, numeric fields can be used directly without any quotes.

Operators Allowed in WHERE Clause

The SQL WHERE clause can be used with different operators. The most frequently used operators are summarized in the following table: