Curriculum

SQL Server WHERE clause

In this tutorial, you’ll learn how to filter rows using the WHERE clause in SQL Server’s T-SQL statement.

When you use the “SELECT” statement in SQL Server to query the results, you’ll get all the rows from the table by default. Sometimes, you might not need all the records but a filtered row based on some conditions. The WHERE clause in SQL Server does that for us. WHERE clause can be used with other types of queries apart from SELECT clauses, including UPDATE, DELETE and INSERT clauses.

Syntax (with SELECT statement)

SELECT
    columns...
FROM
    TABLENAME
WHERE
    searchcondition;

Parameters

The WHERE clause has just one parameter i.e., the conditions that must be met for the records to be filtered. The WHERE clause only returns the rows from the table when the search conditions return true. The search condition is a combination of logical expressions or predicate.

Let’s see some examples of the WHERE clause with the select statement. We will be using the Person.Person table from the AdventureWorks database for the demo.

1. WHERE clause with single equality condition

The below SQL statement retrieves all the People record from the Person table which contains the EmailPromotions of 1

SELECT *
  FROM [Person].[Person]
WHERE EmailPromotion = 1

In the above SQL query, the WHERE clause will only return the results of the records that has EmailPromotion =1. Since, we have used the * in the SELECT statement, all the fields from the Person table will be returned along with the filtered records.

2. WHERE clause with multiple conditions using AND operator

The below T-SQL query will return records from the Person table that matches two conditions EmailPromotion=1 and PersonType = ‘EM’. The logical operator “AND” is used in the filter and hence only when both the conditions are met, the records will be returned.

SELECT *
  FROM [AdventureWorks2019].[Person].[Person]
WHERE EmailPromotion=1 and PersonType = 'EM'

3. WHERE clause that meets any of the conditions using OR operator

The below T-SQL query will return records from the Person table that matches two conditions EmailPromotion=1 and PersonType = ‘EM’. The logical operator “OR” is used in the filter and hence even if one of the condition is met, the records will be returned.

SELECT * FROM 
[Person].[Person] 
WHERE EmailPromotion=1 OR PersonType = 'EM'

4. WHERE clause with comparison operator

The below T-SQL query will return all records from the Person table that has a BusinessEntity of greater than or equals to 750. The greater than or equals to is one of the examples for comparison operator.

SELECT *
  FROM [Person].[Person]
WHERE BusinessEntityID >= 750

5. WHERE clause with the IN operator

The below T-SQL query uses the IN operator to find Person whose BusinessEntityId is 1 or 3 or 5.

SELECT *
  FROM [Person].[Person]
WHERE BusinessEntityID IN (1,3,5)

6. WHERE clause with the BETWEEN operator

The below T-SQL query returns all the PERSON records whose BusinessEntityId is between 1 to 5.

SELECT *
  FROM [Person].[Person]
WHERE BusinessEntityID BETWEEN 1 AND 5

7. WHERE clause with LIKE operator

The below T-SQL query returns all the Person records whose FirstName contains Ken.

SELECT *
  FROM [Person].[Person]
WHERE FirstName LIKE '%Ken%'

8. WHERE clause in JOIN tables

The below T-SQL query joins two tables that are connected and returns the Person and BusinessEntityContact records that has an Person Id of 291.

SELECT *
  FROM [Person].[Person] AS PER
  INNER JOIN Person.BusinessEntityContact AS ADDR on ADDR.PersonID = per.BusinessEntityID
WHERE PER.BusinessEntityID = 291

Microsoft SQL Server