The WHERE command in SQL is used in queries to filter out data that meets a specific condition. This allows you to extract only the desired rows from a table in a database. With this command, you can select a subset of data according to precise criteria. Find out more about the syntax of the WHERE command in SQL, its use cases and its comparison operators to carry out your queries more efficiently.
The syntax of the WHERE command in SQL
The WHERE command consists of a SELECT command and the WHERE condition.
The most common syntax is as follows:
SELECT column_name1, column_name2 FROM table_name WHERE a_condition
Let’s break down this SQL query:
SELECT will select the columns named column_name1 and column_name2
FROM will select the table named table_name
WHERE adds a condition: select only what respects the condition une_condition.
Some examples of WHERE commands
To make the WHERE command more concrete and better understand how it works, let’s apply it to a specific case. In this example, we’re using a customer table. It simulates a table containing a company’s customers and their number of orders.
| ID | First Name | Last Name | Order Count | City | |
|---|---|---|---|---|---|
| 1 | Marie | Nevier | mnevier@example.com | 7 | Paris |
| 2 | Gaëtan | Doucle | gdoucle@example.com | 4 | Nantes |
| 3 | Louis | Chenard | lchenard@example.com | 1 | Lyon |
| 4 | Olivia | Caffin | ocaffin@example.com | 9 | Bordeaux |
| 5 | Camille | Loutran | cloutran@example.com | 3 | Paris |
Example of selection by city
By using the syntax of the WHERE command and adding the right elements, you can filter this table as you see fit. For example, to select only customers from the city of Paris, we’d run the query :
SELECT * FROM customers WHERE city = ‘Paris’.
This query returns the following selection:
| ID | First Name | Last Name | Order Count | City | |
|---|---|---|---|---|---|
| 1 | Marie | Nevier | mnevier@example.com | 7 | Paris |
| 2 | Camille | Loutran | cloutran@example.com | 3 | Paris |
By using the syntax of the WHERE command and adding the right elements, you can filter this table as you see fit. For example, to select only customers from the city of Paris, we’d run the query :
SELECT * FROM customers WHERE city = ‘Paris’.
This query returns the following selection:
Example of selection by number of orders
For a physical or online business, it can be useful to filter customers by number of orders. Let’s take the initial table and run the following query:
SELECT * FROM customers WHERE nbr_orders >= 4
This query returns all customers who have placed 4 or more orders:
| ID | First Name | Last Name | Order Count | City | |
|---|---|---|---|---|---|
| 1 | Marie | Nevier | mnevier@example.com | 7 | Paris |
| 2 | Gaëtan | Doucle | gdoucle@example.com | 4 | Nantes |
| 3 | Olivia | Caffin | ocaffin@example.com | 9 | Bordeaux |
Comparison operators in the WHERE command
In our two examples, we’ve used the = and >= operators to filter the results in the SQL query. But these are not the only operators that can be used with the WHERE command. See the table below for a list of the most commonly used operators:
| Operator | Operator Description |
|---|---|
| = | Equal |
| <> or != | Not Equal |
| < | Less Than |
| > | Greater Than |
| <= | Less Than or Equal |
| >= | Greater Than or Equal |
| IN | List of Possible Values |
| BETWEEN | Values within a Data Range (value range or date range) |
| LIKE | Matches the beginning, middle, or end of a word |
| IS NULL | Value is Null |
| IS NOT NULL | Value is Not Null |
Conclusion on SQL WHERE
In short, WHERE is a particularly useful SQL command for selecting the rows of a table that meet a specific condition. The various operators allow you to use the WHERE command to filter different values, such as numbers, dates or words.
💡Related articles:
