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:
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:

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.
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.

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.

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:
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.G) Finding rows whose values contain a string
The following example uses the
LIKE
operator to find products whose name contains the string Cruiser
:
In this tutorial, you have learned how to use the SQL Server
WHERE
clause to filter rows based on one or more conditions.
0 Comments