SQL WHERE Clause

SQL WHERE Clause can be used to select specific records from a database table based on some conditions, on which an SQL operation is performed.

  • It can be used on different types of SQL statements, like SELECT, UPDATE, DELETE, etc.,

In the previous chapters, we have created and added data to a table.

Now, let's see how to use conditions on the SQL statements to perform the operations on specific records of a table.

Syntax

Here is the basic syntax of a WHERE clause.

  • The table name is represented as table_name.
  • If the condition returns TRUE for a record, it is included, else excluded from the list of selected records.
  • It can be used on the commonly used queries like SELECT, UPDATE, DELETE, etc.,
SELECT * FROM table_name WHERE condition;
UPDATE table_name SET column = value WHERE condition;
DELETE FROM table_name WHERE condition;

Table Data

Let's consider the below data in the table employees before executing any of the below queries.

employee_id employee_name gender birth_date hire_date salary
1 Robin Hood M 1990-10-10 2010-10-15 25000
2 Tony Blank M 1982-08-07 2010-01-05 89000
3 Andrew Russel M 1998-05-04 2012-02-20 28000
4 James Cooper M 2000-10-20 2015-05-10 45000
5 Rose Cutler F 1985-08-08 2015-06-21 65000

Select All with WHERE Clause

Use the below query to retrieve data from all columns of a table.

Run this on IDE

SELECT * FROM employees WHERE employee_id IN (1,2,3);

After successful execution, the output looks something like this.

employee_id employee_name gender birth_date hire_date salary
1 Robin Hood M 1990-10-10 2010-10-15 25000
2 Tony Blank M 1982-08-07 2010-01-05 89000
3 Andrew Russel M 1998-05-04 2012-02-20 28000

Select Columns with WHERE Clause

Use the below query to retrieve data from specific columns of a table, which can be used when we don't need data from all the columns.

Run this on IDE

SELECT employee_id, employee_name, hire_date, salary FROM employees WHERE employee_id IN (1,2,3);

After successful execution, the output looks something like this.

employee_id employee_name hire_date salary
1 Robin Hood 2010-10-15 25000
2 Tony Blank 2010-01-05 89000
3 Andrew Russel 2012-02-20 28000

Update with WHERE Clause

Use the below query to update specific records in a table, using the WHERE condition to select records.

Run this on IDE

UPDATE employees SET salary = 20000 WHERE employee_id IN (1,2,3);

After successful execution, the table data shows the updated salary against the updated records.

employee_id employee_name gender birth_date hire_date salary
1 Robin Hood M 1990-10-10 2010-10-15 20000
2 Tony Blank M 1982-08-07 2010-01-05 20000
3 Andrew Russel M 1998-05-04 2012-02-20 20000
4 James Cooper M 2000-10-20 2015-05-10 45000
5 Rose Cutler F 1985-08-08 2015-06-21 65000

Delete with WHERE Clause

Use the below query to delete specific records from a table, using the WHERE condition to select records.

Run this on IDE

DELETE FROM employees WHERE employee_id IN (1,2,3);

After successful execution, the table doesn't contain the deleted records.

employee_id employee_name gender birth_date hire_date salary
4 James Cooper M 2000-10-20 2015-05-10 45000
5 Rose Cutler F 1985-08-08 2015-06-21 65000

Operators Allowed on WHERE Clause

SQL supports a number of different types of operators that can be used in the WHERE clause, and here is a list of the most commonly used.

See SQL Operators for more details on operators.

Operator Description Example
= Equal WHERE salary = 20000
> Greater than WHERE salary > 20000
< Less than WHERE salary < 20000
>= Greater than or equal WHERE salary >= 20000
<= Less than or equal WHERE salary <= 20000
LIKE Simple pattern matching WHERE employee_name LIKE 'Tony%'
IN Check whether a specified value matches any value in a list or subquery WHERE employee_id IN (1,2,3)
BETWEEN Check whether a specified value is within a range of values WHERE employee_id BETWEEN 1 AND 3

Overall

We now know how to create and execute statements with the WHERE clauses.

Related Links