The WHERE and HAVING clauses in a SELECT statement control the rows from the source tables that are used to build the result set. WHERE and HAVING are filters. They specify a series of search conditions, and only those rows that meet the terms of the search conditions are used to build the result set. Those rows meeting the search conditions are said to be qualified to participate in the result set. For example, the WHERE clause in this SELECT statement qualifies the rows only where the region is Washington State:
SELECT CustomerID, CompanyName
FROM Northwind.dbo.Customers
WHERE Region = 'WA'
The HAVING clause is typically used in conjunction with the GROUP BY clause, although it can be specified without GROUP BY. The HAVING clause specifies further filters that are applied after the WHERE clause filters. For example, this WHERE clause only qualifies orders selling a product with a unit price exceeding $100, and the HAVING clause further restricts the result to only the orders that includes 100 or more units:
The search conditions, or qualifications, in the WHERE and HAVING clauses can include:
Comparison operators (such as =, < >, <, and >). For example, this query retrieves the rows from the Products table for the products that are in product category 2:
SELECT ProductID, ProductName
FROM Northwind.dbo.Products
WHERE CategoryID = 2
ORDER BY ProductID
Ranges (BETWEEN and NOT BETWEEN). For example, this query retrieves rows from the Products table with categories from 2 to 4:
SELECT CategoryID, ProductID, ProductName
FROM Northwind.dbo.Products
WHERE CategoryID BETWEEN 2 and 4
ORDER BY CategoryID, ProductID
Lists (IN, NOT IN). For example, this query retrieves rows from the Products table in which the Category ID matches one in a list of IDs:
SELECT CategoryID, ProductID, ProductName
FROM Northwind.dbo.Products
WHERE CategoryID IN (1,4,5,7)
ORDER BY CategoryID, ProductID
Pattern matches (LIKE and NOT LIKE). For example, this query retrieves rows from the Products table in which the product name starts with the letters Ch:
SELECT CategoryID, ProductID, ProductName
FROM Northwind.dbo.Products
WHERE ProductName LIKE 'Ch%'
ORDER BY CategoryID, ProductID
--------------------------------------------------------------------------------
Note The only WHERE conditions that you can use on text columns are functions that return another data type, such as PATINDEX(), or the operators, such as IS NULL, IS NOT NULL, LIKE, and NOT LIKE.
--------------------------------------------------------------------------------
Null values (IS NULL and IS NOT NULL). For example, this query retrieves rows from the Customers table in which the customer's region is not NULL:
SELECT CompanyName, City, Region, Country
FROM Northwind.dbo.Customers
WHERE Region IS NOT NULL
ORDER BY CompanyName
--------------------------------------------------------------------------------
Note Use caution when comparing null values. For example, specifying = NULL is not the same as specifying IS NULL. For more information, see Null Values.
--------------------------------------------------------------------------------
All records (=ALL, >ALL, <= ALL, ANY). For example, this query retrieves order and product IDs from the Order Details table in which the quantity of the product shipped is larger than the quantity shipped for any product in category 1:
Combinations of these conditions (AND, OR, NOT). For example, this query retrieves all products for which either the stock level is lower than the reorder point or the product comes from supplier 15 and is in category 4:
--------------------------------------------------------------------------------
Note When you search for a Unicode string in a WHERE clause, place the N character before the search string, for example:
SELECT CompanyName, ContactName, Phone, Fax
FROM Northwind.dbo.Customers
WHERE CompanyName = N'Berglunds snabbk p'
/////////////////////////////
Comparison Search Conditions (Transact-SQL)
Microsoft SQL Server uses these comparison operators.
Operator
Meaning
=
Equal to
>
Greater than
<
Less than
>=
Greater than or equal to
<=
Less than or equal to
< >
Not equal to
!=
Not equal to (not ISO standard)
!<
Not less than (not ISO standard)
!>
Not greater than (not ISO standard)
/////
Comparison operators are specified between two expressions. For example, to retrieve the names of only those products for which the unit price is greater than $50, use:
SELECT ProductName
FROM Northwind.dbo.Products
WHERE UnitPrice > $50.00
When you compare character string data, the logical sequence of the characters is defined by the sort order specified during SQL Server Setup. The result of comparison operators such as < and > are controlled by the character sequence defined in the sort order. For Unicode columns, the results of comparison operators are controlled by the Unicode collation order specified during the Setup program. (For more information about specifying sort orders and Unicode collation orders during installation, see Sort Order and Unicode Collation.)
Trailing blanks are ignored in comparisons in non-Unicode data; for example, these are equivalent:
WHERE au_lname = 'White'
WHERE au_lname = 'White '
WHERE au_lname = 'White' + SPACE(1)
The use of NOT negates an expression. For example, this query finds all products that do not have a unit price over $50, which is logically the same as asking for all products that have a unit price of $50 or lower:
SELECT ProductID, ProductName, UnitPrice
FROM Northwind.dbo.Products
WHERE NOT UnitPrice < $50
ORDER BY ProductID
////////////////////////////////////////////
Using Range Search Conditions in SQL
//
A range search is one that returns all values between two specified values. Inclusive ranges return any values that match the two specified values. Exclusive ranges do not return any values the match the two specified values.
The BETWEEN keyword specifies an inclusive range to search. For example, this SELECT returns all products whose units in stock is between 15 and 25:
SELECT UnitsInStock, ProductID, ProductName
FROM Northwind.dbo.Products
WHERE UnitsInStock BETWEEN 15 AND 25
ORDER BY UnitsInStock
The results of this SELECT statement contains any products that have either 15 or 25 units in stock.
To specify an exclusive range, use the greater-than and less-than operators (> and <). The following query using the greater-than and less-than operators returns different results than the last example because these operators do not include rows matching the values that limit the range.
SELECT UnitsInStock, ProductID, ProductName
FROM Northwind.dbo.Products
WHERE UnitsInStock > 15 AND UnitsInStock < 25
ORDER BY UnitsInStock
NOT BETWEEN finds all rows outside the range you specify. Use this query to find all products for which the number of units in stock are outside the 15 to 25 range:
SELECT UnitsInStock, ProductID, ProductName
FROM Northwind.dbo.Products
WHERE UnitsInStock NOT BETWEEN 15 AND 25
ORDER BY UnitsInStock
Using List Search Conditions in SQL
The IN keyword allows you to select rows that match any one of a list of values. For example, without IN, if you want a list of the names and states of all authors who live in California, Indiana, or Maryland, you would need this query:
SELECT ProductID, ProductName
FROM Northwind.dbo.Products
WHERE CategoryID = 1 OR CategoryID = 4 OR CategoryID = 5
You can also get the same results with less typing if you use IN:
SELECT ProductID, ProductName
FROM Northwind.dbo.Products
WHERE CategoryID IN (1, 4, 5)
The items following the IN keyword must be separated by commas and be enclosed in parentheses. For more uses of the IN keyword in nested queries, see nested subqueries.
This query finds all au_ids in the titleauthor table for authors who make less than 50 percent of the royalty on any one book, and then selects from the authors table all author names with au_ids that match the results from the titleauthor query:
The results show that several authors fall into the less than 50 percent category.
This query finds the names of authors who do not make less than 50 percent of the royalties on at least one book:
NOT IN finds the authors who do not match the items in the values list.
////////////////////////////////////////////////
Pattern Matching in Search Conditions
The LIKE keyword searches for character string, date, or time values that match a specified pattern. For more information, see Data Types. The LIKE keyword uses a regular expression to contain the pattern the values are matched against. The pattern contains the character string to search for, which can contain any combination of four wildcards.
Wildcard
Meaning
%
Any string of zero or more characters
_
Any single character
[ ]
Any single character within the specified range (for example, [a-f]) or set (for example, [abcdef])
[^]
Any single character not within the specified range (for example, [^a - f]) or set (for example, [^abcdef])
Enclose the wildcard(s) and the character string in single quotation marks, for example:
LIKE Mc% searches for all strings that begin with the letters Mc (McBadden).
LIKE %inger searches for all strings that end with the letters inger (Ringer, Stringer).
LIKE %en% searches for all strings that contain the letters en anywhere in the string (Bennet, Green, McBadden).
LIKE _heryl searches for all six-letter names ending with the letters heryl (Cheryl, Sheryl).
LIKE [CK]ars[eo]n searches for Carsen, Karsen, Carson, and Karson (Carson).
LIKE [M-Z]inger searches for all names ending with the letters inger that begin with any single letter from M to Z (Ringer).
LIKE M[^c]% searches for all names beginning with the letter M that don t have the letter c as the second letter (MacFeather).
This query finds all phone numbers in the authors table that have area code 415:
SELECT phone
FROM pubs.dbo.authors
WHERE phone LIKE '415%'
You can use NOT LIKE with the same wildcards. To find all phone numbers in the authors table that have area codes other than 415, use either of these equivalent queries:
SELECT phone
FROM pubs.dbo.authors
WHERE phone NOT LIKE '415%'
-- Or
SELECT phone
FROM pubs.dbo.authors
WHERE NOT phone LIKE '415%'
The IS NOT NULL clause can be used with wildcards and the LIKE clause. For example, this query retrieves telephone numbers from the authors table in which the telephone number begins with 415 and IS NOT NULL:
USE pubs
SELECT phone
FROM authors
WHERE phone LIKE '415%' and phone IS NOT NULL
--------------------------------------------------------------------------------
Important The output for statements involving the LIKE keyword depends on the sort order chosen during installation. For information about the effects of different sort orders, see Sort Order.
--------------------------------------------------------------------------------
The only WHERE conditions that you can use on text columns are LIKE, IS NULL, or PATINDEX.
Wildcards used without LIKE are interpreted as constants rather than as a pattern, that is, they represent only their own values. The following query attempts to find any phone numbers that consist of the four characters 415% only. It will not find phone numbers that start with 415. For information about constants, see Using Constants.
SELECT phone
FROM pubs.dbo.authors
WHERE phone = '415%'
Another important consideration in using wildcards is their effect on performance. If a wildcard begins the expression, an index cannot be used. (Just as you wouldn t know where to start in a phone book if given the name %mith , not Smith .) A wildcard in or at the end of an expression does not preclude use of an index (just as in a phone book, you d know where to search if the name was Samuel% , regardless of whether the names Samuels and Samuelson are both there).
How to Search for Wildcard Characters in SQL
/////
You can search for wildcard characters. There are two methods for specifying a character that would ordinarily be a wildcard:
Use the ESCAPE keyword to define an escape character. When the escape character is placed in front of the wildcard in the pattern, the wildcard is interpreted as a character. For example, to search for the string 5% anywhere in a string, use:
WHERE ColumnA LIKE '%5/%%' ESCAPE '/'
In this LIKE clause, the leading and ending percent signs (%) are interpreted as wildcards, and the percent sign preceded by a slash (/) is interpreted as the % character.
Use square brackets ([ ]) to enclose the wildcard by itself. To search for a dash (-), rather than using it to specify a search range, use the dash as the first character inside a set of brackets:
WHERE ColumnA LIKE '9[-]5'
The table shows the use of wildcard characters in the SQL statement (enclosed in square brackets)
SQL Wildcard Characters
Symbol
Meaning
LIKE 5[%]
5%
LIKE 5%
5 followed by any string of 0 or more characters
LIKE [_]n
_n
LIKE _n
an, in, on (and so on)
LIKE [a-cdf]
a, b, c, d, or f
LIKE [-acdf]
-, a, c, d, or f
LIKE [ [ ]
[
LIKE ']'
]
When string comparisons are performed with LIKE, all characters in the pattern string are significant, including every leading and/or trailing blank (space). If a comparison to return all rows with a string LIKE abc (abc followed by a single space) is requested, a row in which the value of that column is abc (abc without a space) is not returned. The reverse, however, is not true. Trailing blanks in the expression to which the pattern is matched are ignored. If a comparison to return all rows with a string LIKE abc (abc without a space) is requested, all rows that start with abc and have zero or more trailing blanks are returned.
Using NULL Comparison Search Conditions in SQL
The value NULL means the data value for the column is unknown or not available. NULL is not synonymous with zero (numeric or binary value), a zero-length string, or blank (character value). Rather, null values allow you to distinguish between a entry of zero (numeric columns) or blank (character columns) and a nonentry (NULL for both numeric and character columns).
NULL can be entered in a column for which null values are permitted (as specified in the CREATE TABLE statement) in two ways:
Microsoft SQL Server automatically enters the value NULL if no data is entered and there is no default or DEFAULT constraint on the column or data type.
The user can explicitly enter the value NULL by typing NULL without quotation marks. If the word NULL is typed into a character column with quotation marks, it is treated as the letters N, U, L, and L, not as a null value.
When null values are retrieved, an application typically displays a string such as NULL, or (NULL), or (null) in the appropriate position. For example, the advance column of the titles table allows null values:
SELECT title_id, type, advance
FROM pubs.dbo.titles
WHERE advance = NULL
Here is the result set:
title_id type advance
-------- ------------ --------------------------
MC3026 UNDECIDED (null)
PC9999 popular_comp (null)
(2 row(s) affected)
Using Comparing Null Values in SQL
//
Care must be taken when comparing null values. The behavior of the comparison depends on the setting of the SET ANSI_NULLS option.
When SET ANSI_NULLS is ON, a comparison in which one or more of the expressions is NULL does not yield either TRUE or FALSE; it yields UNKNOWN. This is because a value that is unknown cannot be compared logically against any other value. This occurs if either an expression is compared to the literal NULL, or if two expressions are compared and one of them evaluates to NULL. For example, this comparison always yields UNKNOWN when ANSI_NULLS is ON:
ytd_sales > NULL
This comparison also yields UNKNOWN any time the variable contains the value NULL:
ytd_sales > @MyVariable
Use the IS NULL or IS NOT NULL clauses to test for a NULL value. This can add complexity to the WHERE clause. For example, the Region column in the Northwind Customers table allows null values. If a SELECT statement is to test for null values in addition to others, it must include an IS NULL clause:
SELECT CustomerID, CompanyName, Region
FROM Northwind.dbo.Customers
WHERE Region IN ('WA', 'SP', 'BC')
OR Region IS NULL
Transact-SQL supports an extension that allows for the comparison operators to return TRUE or FALSE when comparing against null values. This option is activated by setting ANSI_NULLS OFF. When ANSI_NULLS is OFF, comparisons such as ColumnA = NULL return TRUE when ColumnA contains a null value and FALSE when ColumnA contains some value besides NULL. Also, a comparison of two expressions that have both evaluated to null values yields TRUE. With ANSI_NULLS set OFF, this SELECT statement returns all the rows in the Customer table for which Region is a null value:
SELECT CustomerID, CompanyName, Region
FROM Northwind.dbo.Customers
WHERE Region = NULL
Null values are always considered equal for the purposes of the ORDER BY, GROUP BY, and DISTINCT keywords, regardless of the ANSI_NULLS setting. Also, a unique index or UNIQUE constraint that allows NULL can contain only one row with a NULL key value. A subsequent row with NULL is rejected. A primary key cannot have NULL in any column that is part of the key.
Computations involving NULL evaluate to NULL because the result must be UNKNOWN if any of the factors is unknown. For example, column1 + 1 evaluates to NULL if column1 is NULL.
When the columns being searched include those defined as allowing null values, you can find null or nonnull values in the database with this pattern:
WHERE column_name IS [NOT] NULL
