Introduction to SQL Server WHERE clause

Introduction to SQL Server WHERE clause

When you use the SELECT statement to query data against a table, you get all the rows of that table, which is unnecessary because the application may only process a set of rows at the time.

To get the rows from the table that satisfy one or more conditions, you use the WHERE clause as follows:

1 2 3 4 5 6

SELECT     select_list FROM     table_name WHERE     search_condition;

In the WHERE clause, you specify a search condition to filter rows returned by the FROM clause. The WHERE clause only returns the rows that cause the search condition to evaluate to TRUE.

The search condition is a logical expression or a combination of multiple logical expressions. In SQL, a logical expression is often called a predicate.

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 evaluates to FALSE or UNKNOWN.

SQL Server WHERE examples

We will use the production.products table from the sample database for the demonstration

A) Finding rows by using a simple equality

The following statement retrieves all products with the category id 1:

1 2 3 4 5 6 7 8 9 10 11 12

SELECT     product_id,     product_name,     category_id,     model_year,     list_price FROM     production.products WHERE     category_id = 1 ORDER BY     list_price DESC;

B) Finding rows that meet two conditions

The following example returns products that meet two conditions: category id is 1 and the model is 2018. It uses the logical operator AND to combine the two conditions.

1 2 3 4 5 6 7 8 9 10 11 12

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;

C) Finding rows by using a comparison operator

The following statement finds the products whose list price is greater than 300 and model is 2018.

1 2 3 4 5 6 7 8 9 10 11 12

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;

D) Finding rows that meet any of two conditions

The following query finds products whose list price is greater than 3,000 or model is 2018. Any product that meets one of these conditions is included in the result set.

1 2 3 4 5 6 7 8 9 10 11 12

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;

Note that the OR operator was used to combine the predicates.

E) Finding rows with the value between two values

The following statement finds the products whose list prices are between 1,899 and 1,999.99:

1 2 3 4 5 6 7 8 9 10 11 12

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;

F) Finding rows that have a value in a list of values

The following example uses the IN operator to find products whose list price is 299.99 or 466.99 or 489.99.

1 2 3 4 5 6 7 8 9 10 11 12

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;

G) Finding rows whose values contain a string

The following example uses the LIKE operator to find products whose name contains the string Cruiser:

1 2 3 4 5 6 7 8 9 10 11 12

SELECT     product_id,     product_name,     category_id,     model_year,     list_price FROM     production.products WHERE     product_name LIKE '%Cruiser%' ORDER BY     list_price;

In this tutorial, you have learned how to use the SQL Server WHERE clause to filter rows based on one or more conditions.