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:
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 TRUEFALSE, 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
Products Table

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.
SQL Server WHERE - match 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.
SQL Server WHERE - comparison operators

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.
SQL Server WHERE - match any of two conditions
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:

SQL Server WHERE - between operator

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.

SQL Server WHERE - IN operator

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.

Post a Comment