The SQL WHERE clause limits how many records are returned or affected by a command. It is used with the SELECT, UPDATE, and DELETE clauses. You can use the AND keyword to specify multiple conditions which you want records affected by your command to meet.
You may want to filter out the results of a query using specific conditions. For example, you may want to get a list of employees who are sales managers. Or you may want to find out who started working for a company after a particular date.
You can use the WHERE clause to filter out specific records while running an SQL command. This statement 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.
SQL WHERE Clause
The SQL WHERE clause filters records that meet a particular condition, or a set of conditions. The WHERE clause comes after the “FROM” clause in an SQL statement.
Here is the syntax for this statement:
SELECT * FROM table WHERE column comparison value;
“SELECT * FROM table” is a standard select query. We use the WHERE statement to limit the records our query returns. “column” refers to the column in which a condition needs to be met. “comparison” and “value” represent the comparison we want to make.
Potential comparisons include:
WHERE name == "Jeff"; WHERE age > 15; WHERE salary < 50000;
You can specify multiple WHERE statements using the SQL AND keyword:
SELECT * FROM table WHERE column1 comparison1 value1 AND column2 comparison2 value2;
Let’s walk through an example of a WHERE statement.
SQL WHERE Example: SELECT
We want to get a list of employees who are senior sales associates. To do so, we could use the following SQL SELECT statement:
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
(2 rows)
All the employees above 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 and whose name is not Adam. We could use the following SQL statement:
SELECT name, salary FROM employees WHERE salary > 35000 AND name <> "Adam";
Our query returns the following list:
name | salary
——-+——–
Geoff | 38000
Emma | 50000
Jonah | 50000
Adam | 38000
(4 rows)
"Career Karma entered my life when I needed it most and quickly helped me match with a bootcamp. Two months after graduating, I found my dream job that aligned with my values and goals in life!"
Venus, Software Engineer at Rockbot
Adam’s name is not in our results set even though he earns over $35,000 per year. This is because we excluded his name with the <> operator in our query.
WHERE SQL Clause: Update and Delete
The SQL WHERE clause works with UPDATE and DELETE statements.
For example, say you want to update the branch names individual employees work for if there is a company restructuring. You could do this using the UPDATE statement.
We want to change Luke’s branch from “Boston” to “Cambridge,” where his new office resides. We can do this using an SQL UPDATE statement:
UPDATE employees SET branch = 'Cambridge' WHERE name = 'Luke';
If we query to find out Luke’s branch, we get the following result set:
name | branch
——+———–
Luke | Cambridge
(1 row)
We can use the WHERE clause with the SQL DELETE command.
Say we wanted to delete the records of every employee who works at the Albany branch, which has been closed. We could do this using the following command:
When we get our list of employees, the number of rows returned is six instead of eight:
DELETE FROM employees WHERE branch = 'Albany';
Our code returns six rows:
name | branch
——–+—————
Mike | Stamford
Hannah | San Francisco
Geoff | San Francisco
Alexis | Boston
Adam | Sacramento
Luke | Cambridge
(6 rows)
SQL 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 clause. These are as follows:
Operator | Description | Example |
= | 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 |
Conclusion
The SQL WHERE clause filters the records affected by a command. You can use the WHERE clause in SQL with the SELECT, UPDATE, and DELETE statements. You can specify multiple WHERE clauses using an AND statement, but you only need to use the WHERE keyword once.
In this tutorial, we covered queries, SQL WHERE clauses, and how to use the clause clauses in an update or delete operation. We discussed the conditional operators that can be used with this clause.
Are you interested in becoming a professional SQL developer? Check out our How to Learn SQL guide. This guide contains a list of top learning resources to help you master SQL.
About us: Career Karma is a platform designed to help job seekers find, research, and connect with job training programs to advance their careers. Learn about the CK publication.