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

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.