Edit

Share via

WHERE (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric SQL database in Microsoft Fabric Preview

Specifies the search condition for the rows returned by the query.

Transact-SQL syntax conventions

Syntax

[ WHERE <search_condition> ]

Arguments

<search_condition>

Defines the condition to be met for the rows to be returned. There's no limit to the number of predicates that can be included in a search condition. For more information about search conditions and predicates, see Search condition.

Examples

The code samples in this article use the AdventureWorks2022 or AdventureWorksDW2022 sample database, which you can download from the Microsoft SQL Server Samples and Community Projects home page.

The following examples show how to use some common search conditions in the WHERE clause.

A. Find a row by using a simple equality

-- Uses AdventureWorksDW
SELECT EmployeeKey, LastName
FROM DimEmployee
WHERE LastName = 'Smith';

B. Find rows that contain a value as part of a string

-- Uses AdventureWorksDW
SELECT EmployeeKey, LastName
FROM DimEmployee
WHERE LastName LIKE '%Smi%';

C. Find rows by using a comparison operator

-- Uses AdventureWorksDW
SELECT EmployeeKey, LastName
FROM DimEmployee
WHERE EmployeeKey <= 500;

D. Find rows that meet any of three conditions

-- Uses AdventureWorksDW
SELECT EmployeeKey, LastName
FROM DimEmployee
WHERE EmployeeKey = 1
      OR EmployeeKey = 8
      OR EmployeeKey = 12;

E. Find rows that must meet several conditions

-- Uses AdventureWorksDW
SELECT EmployeeKey, LastName
FROM DimEmployee
WHERE EmployeeKey <= 500
      AND LastName LIKE '%Smi%'
      AND FirstName LIKE '%A%';

F. Find rows that are in a list of values

-- Uses AdventureWorksDW
SELECT EmployeeKey, LastName
FROM DimEmployee
WHERE LastName IN ('Smith', 'Godfrey', 'Johnson');

G. Find rows that have a value between two values

-- Uses AdventureWorksDW
SELECT EmployeeKey, LastName
FROM DimEmployee
WHERE EmployeeKey BETWEEN 100 AND 200;

Related content