Summary: in this tutorial, you will learn how to use the SQL Server WHERE
clause to filter rows returned by a query.
Introduction to SQL Server WHERE clause
When you use the SELECT
statement to retrieve data from a table, you select all the rows of that table, which is unnecessary because the application may only need to process a set of rows at the time.
To retrieve the rows from the table that satisfy one or more conditions, you use the WHERE
clause as follows:
SELECT
select_list
FROM
table_name
WHERE
search_condition;
Code language: SQL (Structured Query Language) (sql)
The search_condition
is a logical expression or a combination of multiple logical expressions. In SQL, a logical expression is often called a predicate.
In the WHERE
clause, you specify a search condition to filter rows returned by the FROM
clause. The WHERE
clause only returns the rows for which the search_condition
evaluates to TRUE
.
Note that SQL Server uses three-valued predicate logic where a logical expression can evaluate to TRUE
, FALSE
, or UNKNOWN
. The WHERE
clause will not return any row that causes the predicate to evaluate to FALSE
or UNKNOWN
.
SQL Server WHERE examples
We will use the production.products
table from the sample database for the demonstration.
1) Using the WHERE clause with a simple equality operator
The following statement uses the WHERE
clause to retrieve the products with the category id 1:
SELECT
product_id,
product_name,
category_id,
model_year,
list_price
FROM
production.products
WHERE
category_id = 1
ORDER BY
list_price DESC;
Code language: SQL (Structured Query Language) (sql)
2) Using the WHERE clause with the AND operator
The following example uses a WHERE clause to find products that meet two conditions: category id is 1, and the model is 2018.
SELECT
product_id,
product_name,
category_id,
model_year,
list_price
FROM
production.products
WHERE
category_id = 1 AND model_year = 2018
ORDER BY
list_price DESC;
Code language: SQL (Structured Query Language) (sql)
In this example, the condition in the WHERE clause uses the logical operator AND
to combine the two conditions.
3) Using WHERE to filter rows using a comparison operator
The following statement finds the products whose list price is greater than 300, and the model is 2018.
SELECT
product_id,
product_name,
category_id,
model_year,
list_price
FROM
production.products
WHERE
list_price > 300 AND model_year = 2018
ORDER BY
list_price DESC;
Code language: SQL (Structured Query Language) (sql)
4) Using the WHERE clause to filter rows that meet any of two conditions
The following query finds products whose list price is greater than 3,000 or whose model is 2018. Any product that meets one of these conditions is included in the result set.
SELECT
product_id,
product_name,
category_id,
model_year,
list_price
FROM
production.products
WHERE
list_price > 3000 OR model_year = 2018
ORDER BY
list_price DESC;
Code language: SQL (Structured Query Language) (sql)
Note that the OR
operator combined the predicates.
5) Using the WHERE clause to filter rows with the value between two values
The following statement finds the products whose list prices are between 1,899 and 1,999.99:
SELECT
product_id,
product_name,
category_id,
model_year,
list_price
FROM
production.products
WHERE
list_price BETWEEN 1899.00 AND 1999.99
ORDER BY
list_price DESC;
Code language: SQL (Structured Query Language) (sql)
6) Using the WHERE clause to filter rows that have a value in a list of values
The following example uses the IN
operator to find products with a list price of 299.99, 466.99, or 489.99.
SELECT
product_id,
product_name,
category_id,
model_year,
list_price
FROM
production.products
WHERE
list_price IN (299.99, 369.99, 489.99)
ORDER BY
list_price DESC;
Code language: SQL (Structured Query Language) (sql)
7) Finding rows whose values contain a string
The following example uses the LIKE
operator to find products whose name contains the string Cruiser
:
SELECT
product_id,
product_name,
category_id,
model_year,
list_price
FROM
production.products
WHERE
product_name LIKE '%Cruiser%'
ORDER BY
list_price;
Code language: SQL (Structured Query Language) (sql)
Summary
- Use the SQL Server
WHERE
clause to filter rows based on one or more conditions.