SQL WHERE Clause Explained with Operators and Examples

What is the WHERE Clause?

The SQL WHERE clause filters the results of a query, returning only the rows where a specified condition evaluates to TRUE. It sits immediately after the FROM clause in a SELECT, UPDATE, or DELETE statement. For example, to find customers over 30:

SELECT * FROM Customers
WHERE Age > 30;

Syntax of the WHERE Clause

The basic syntax of the WHERE clause can be given with the SELECT, UPDATE or DELETE command followed by a condition.

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

The condition in the WHERE clause could be equality, or it could be any condition like comparison or range. The WHERE clause basically returns the rows where condition is TRUE.

Demo database

We will use the following Customers table to demonstrate how different WHERE conditions evaluate in practice:

Comparison operators

Comparison operators (=, >, <, >=, <=,!= or <>) compare a column against a specific value. For example, this query selects all records where the customer's age is greater than 30:

SELECT * FROM Customers
WHERE Age > 30;

BETWEEN, IN and LIKE

These operators are used for ranges, lists, and pattern matching:

• BETWEEN: Filters for values within a specific inclusive range. WHERE Age BETWEEN 25 AND 35
• IN: Checks if a value matches any value in a provided list. WHERE City IN ('London', 'Paris')
• LIKE: Searches for a pattern, typically using wildcards like %. WHERE City LIKE 'L%'

-- Selects customers in either London or Paris
SELECT * FROM Customers
WHERE City IN ('London', 'Paris');

Filtering on NULL values

Because a NULL value represents missing or unknown data, you cannot use standard comparison operators like = to filter for it. Instead, you must use the IS NULL or IS NOT NULL operators.

SELECT * FROM Customers
WHERE City IS NOT NULL;

Combining conditions with AND, OR and NOT

You can combine multiple conditions within a single WHERE clause using logical operators:

• AND: Returns a row if all combined conditions are TRUE.
• OR: Returns a row if any of the separated conditions are TRUE.
• NOT: Reverses a condition, returning TRUE if the condition is FALSE.

SELECT * FROM Customers
WHERE Age > 25 AND City = 'New York';

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

Common Mistakes

While using the WHERE clause, it's quite common for beginners to make some errors. Here are a few common mistakes:

• Comparing to NULL with =: NULL represents missing data, so WHERE column = NULL will always fail. You must use WHERE column IS NULL.
• Mixing AND and OR without parentheses: AND takes precedence over OR. Always use parentheses when mixing them to ensure your conditions evaluate in the intended order.
• Filtering aggregates in WHERE: The WHERE clause filters individual rows before aggregation. If you need to filter on an aggregate function like COUNT() or SUM(), you must use the HAVING clause instead.

Frequently Asked Questions

1. What is the difference between the WHERE and HAVING clause in SQL? The WHERE clause introduces a condition on individual rows; the HAVING clause introduces a condition on aggregations or results of a selection where a single result, such as average, sum, count, etc., has to be compared against a given condition.
2. Can we use the WHERE clause with the UPDATE and DELETE commands? Yes, the WHERE clause can be used with SELECT, UPDATE, and DELETE commands to filter & manipulate data on the basis of a certain condition.
3. Can I use two or more conditions in a WHERE clause? Yes, you can use multiple conditions in the WHERE clause using AND or OR operators. Make sure to use parentheses for clarity when combining these operators.

Practice Questions

To reinforce what you've learned, try out these practice questions:

1. Write a query to find all customers whose names start with 'J'.
2. Write a query to find all customers who live in 'New York' and their age is above 25.
3. Write a query to find all products that have a price between $100 and $500.
4. Write a query to find all employees who do not work in the 'Sales' department.

Conclusion

The SQL WHERE clause is a powerful tool that allows you to filter the results of your SQL queries. By using different operators, you can create complex conditions to retrieve specific data from your databases. Keep practicing with different operators and conditions to improve your SQL skills.

Thank you for following along with this tutorial. If you are still finding your feet, the SQL tutorial for beginners walks through the rest of the SELECT statement.

To run these queries against a schema of your own, DbSchema's free Community Edition reverse-engineers your database into an interactive diagram and includes an SQL editor - download DbSchema.

Happy coding!

Remember: Practice makes perfect! The more you use these commands in different combinations, the more comfortable you'll become with the SQL syntax.