Dela via


WHERE (Transact-SQL)

Specifies the search condition for the rows returned by a DELETE, MERGE, SELECT, or UPDATE statement in SQL Server 2008 R2. Use this clause to limit the number of rows returned by or affected by the statement.

Topic link iconTransact-SQL Syntax Conventions

Syntax

[ WHERE <search_condition> ]

Arguments

  • < search_condition >
    Defines the condition to be met for the rows to be returned. There is 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 (Transact-SQL).

Examples

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

A. Finding a row by using a simple equality

USE AdventureWorks2008R2;
GO
SELECT ProductID, Name
FROM Production.Product
WHERE Name = 'Blade' ;
GO

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

SELECT ProductID, Name, Color
FROM Production.Product
WHERE Name LIKE ('%Frame%');
GO

C. Finding rows by using a comparison operator

SELECT ProductID, Name
FROM Production.Product
WHERE ProductID <= 12 ;
GO

D. Finding rows that meet any of three conditions

SELECT ProductID, Name
FROM Production.Product
WHERE ProductID = 2
OR ProductID = 4 
OR Name = 'Spokes' ;
GO

E. Finding rows that must meet several conditions

SELECT ProductID, Name, Color
FROM Production.Product
WHERE Name LIKE ('%Frame%')
AND Name LIKE ('HL%')
AND Color = 'Red' ;
GO

F. Finding rows that are in a list of values

SELECT ProductID, Name, Color
FROM Production.Product
WHERE Name IN ('Blade', 'Crown Race', 'Spokes');
GO

G. Finding rows that have a value between two values

SELECT ProductID, Name, Color
FROM Production.Product
WHERE ProductID BETWEEN 725 AND 734;
GO