SQL WHERE Clause – Tutorial with Examples

The WHERE clause is an essential component of SQL and is used to filter data from a database table. It is used in conjunction with the SELECT, UPDATE, and DELETE statements to specify which rows of data are to be returned, updated or deleted, respectively. In this article, we will discuss the basics of the WHERE clause and how it is used in SQL.

Syntax

The basic syntax of the WHERE clause is as follows:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

In the example above, we are retrieving columns (column1 and column2) from a table named `table_name`. The `SELECT` keyword is followed by the list of column names separated by commas. The `FROM` keyword is used to specify the name of the table that contains the data we want to retrieve. The `WHERE` clause is used to filter the data based on a specified condition. The condition can be any valid expression that evaluates to a boolean value (true or false).

Example

Let’s say we have a table named `employees` with the following data:

id	name	salary	department
1	John Doe	50000	IT
2	Jane Doe	60000	Sales
3	Jim Smith	65000	IT
4	Samantha Smith	70000	Marketing

If we want to retrieve the name and salary of employees whose salary is greater than or equal to 60000, we can use the following SELECT statement with the WHERE clause:

SELECT name, salary
FROM employees
WHERE salary >= 60000;

This will return the following result:

name	salary
Jane Doe	60000
Jim Smith	65000
Samantha Smith	70000

As we can see, only the employees with a salary greater than or equal to 60000 are returned. The WHERE clause has filtered the data based on the specified condition.

Operators

The WHERE clause uses various operators to filter data, such as:

• Equal to (=) – Returns rows where the value of the specified column is equal to a given value
• Not equal to (!= or <>) – Returns rows where the value of the specified column is not equal to a given value
• Greater than (>) – Returns rows where the value of the specified column is greater than a given value
• Less than (<) – Returns rows where the value of the specified column is less than a given value
• Greater than or equal to (>=) – Returns rows where the value of the specified column is greater than or equal to a given value
• Less than or equal to (<=) – Returns rows where the value of the specified column is less than or equal to a given value
• Between – Returns rows where the value of the specified column is within a specified range
• Like – Returns rows where the value of the specified column matches a specified pattern
• In – Returns rows where the value of the specified column is found within a set of specified values
• Not in – Returns rows where the value of the specified column is not found within a set of specified values
• Is Null – Returns rows where the value of the specified column is NULL
• Is Not Null – Returns rows where the value of the specified column is not NULL

Each of these operators can be used in the WHERE clause to filter data based on specific conditions.

Combining Conditions

Multiple conditions can be combined in the WHERE clause using the logical operators AND and OR. The AND operator returns only the rows that satisfy both conditions, while the OR operator returns the rows that satisfy either condition.

SELECT name, salary
FROM employees
WHERE department = 'IT' AND salary >= 60000;

This SELECT statement with the WHERE clause will return the name and salary of employees who work in the IT department and have a salary greater than or equal to 60000:

name	salary
Jim Smith	65000

In this case, only one row is returned because the conditions specified in the WHERE clause are both satisfied by the same row in the table.

Conclusion

The WHERE clause is an important aspect of SQL that allows you to filter data based on specific conditions. It is used in conjunction with the SELECT, UPDATE, and DELETE statements to retrieve, modify, or delete specific data from a database table. Understanding how to use the WHERE clause is crucial in managing data in SQL databases. In this article, we have covered the basic syntax and usage of the WHERE clause, the various operators that can be used, and how to combine multiple conditions using logical operators. With this knowledge, you should be able to effectively filter and manage data in your SQL databases.