When you’re querying a database, you may want to filter out the results using specific conditions. For example, you may want to get a list of employees who are sales managers, or who started working for a company after a particular date. Or you may wish to update or delete records, but only if they meet your requirements.
That’s where the SQL WHERE clause comes in. You can use the WHERE clause to filter out specific records while running an SQL command. WHERE can work in conjunction with SELECT, UPDATE, and DELETE clauses.
In this guide, we’ll cover how to use the WHERE clause to filter records in SQL. We’ll also discuss the conditional operators you can use with the command.
To retrieve data from a database, you need to run a query. SQL queries almost always start with the SELECT statement, which allows you to specify which table columns the query should return. Queries often include a FROM statement as well, which specifies which table your query will run on.
Here’s the syntax for an SQL query:
SELECT column_name FROM table_name WHERE conditions_are_met;
Let’s use an example to illustrate this in action. The following query will return a list of the names of every employee in the “employees” table:
SELECT name FROM employees;
Our query returns the following:
If you want to select multiple columns from the same table, you can do so by separating the column names with a comma. Or, if you’re going to select every column, you can use an asterisk (*), which represents all columns within an SQL table.
The query we have used above returns the name of every employee. But what if we only want to get the names of employees who work in sales, or employees who work for a specific branch? We can use the WHERE operator to accomplish this goal.
WHERE is used in SQL queries to filter records that meet a particular condition, or a set of conditions. The WHERE clause comes after the “FROM” clause in an SQL statement, and usually uses the following syntax:
… WHERE column_name comparison value;
So, let’s say that we want to get a list of employees who are senior sales associates. We could use the following SQL statement to get that information, which filters employees by their title:
SELECT name, title FROM employees WHERE title = 'Senior Sales Associate';
The query returns the following rows that meet our criteria:
name | title
Geoff | Senior Sales Associate
Adam | Senior Sales Associate
As you can see, our query only returns information about employees who have the title “Senior Sales Associate.”
Let’s use another example. Say we wanted to get a list of every employee who earns over $35,000 per year. We could use the following SQL statement:
SELECT name, salary FROM employees WHERE salary > 35000;
Our query returns the following list:
name | salary
Geoff | 38000
Emma | 50000
Jonah | 50000
Adam | 38000
SQL WHERE Update and Delete
The SQL WHERE clause also works with UPDATE and DELETE statements. For example, you may want to update the branch names individual employees work for if there is a company restructuring. Or you may want to delete all employees who are not affiliated with any company department and have been made redundant.
Here’s an example of an SQL UPDATE command that would change Luke’s branch from “Boston” to “Cambridge,” where his new office resides:
UPDATE employees SET branch = 'Cambridge' WHERE name = 'Luke';
Now, if we query to find out Luke’s branch, we get the following result set:
name | branch
Luke | Cambridge
We can also use the SQL WHERE clause with the DELETE command. If we wanted to delete the records of every employee who works at the Albany branch, which has been closed, we could use the following command:
DELETE FROM employees WHERE branch = 'Albany';
Now, when we get our list of employees, the number of rows returned is six instead of eight:
name | branch
Mike | Stamford
Hannah | San Francisco
Geoff | San Francisco
Alexis | Boston
Adam | Sacramento
Luke | Cambridge
SQL WHERE Operators
The SQL WHERE clause uses logical operators to filter out records. In our first example, we used the “is equal to” operator to check for the names of senior sales associates.
There are a number of logical operators we can use to filter out records when utilizing the WHERE clause. These are as follows:
|=||Is equal to||WHERE name = ‘Jake’|
|>||Is greater than||age > 20|
|<||Is less than||age < 20|
|>=||Greater than or equal to||salary => 50000|
|<=||Less than or equal to||salary <= 40000|
|LIKE||Pattern matching||name LIKE ‘Jo*’|
|IN||Check if a specified value matches any within a list||title IN (‘Sales Associate’, ‘Director of Sales’)|
|BETWEEN||Check if the specified value is within a range of other values||employee_month_awards BETWEEN 1 AND 5|
The SQL WHERE clause can be used to filter records when you’re running an SQL command.
In this tutorial, we covered queries, SQL WHERE clauses, and how to use WHERE clauses in an update or delete operation. We also discussed the conditional operators that can be used with the WHERE clause.
Now you’re ready to start filtering records in your SQL commands like a pro!