SQL Server WHERE Clause

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 #

The SELECT statement retrieves all rows from a table. However, this is often unnecessary because the application may only need to process a subset of rows at the time.

To retrieve rows that satisfy one or more conditions, you use the WHERE clause in the SELECT statement.

Here’s the syntax of the WHERE clause:

SELECT
    select_list
FROM
    table_name
WHERE
    search_condition;Code language: SQL (Structured Query Language) (sql)

In this syntax, the search_condition is a logical expression or a combination of multiple logical expressions. In SQL, a logical expression is also known as 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 query uses a WHERE clause to retrieve 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)

Output: