The WHERE clause specifies the rows used by its command (either a SELECT, DELETE, or UPDATE command).

The syntax of the WHERE clause is as follows:

WHERE {OldStyleOuterJOIN | RowSearchCondition}

OldStyleOuterJOIN was used in earlier versions of SQL. It allowed left and outer join conditions to be specified in the WHERE clause with the *= and =* operators. However this produced ambiguous results, whereas joins specified in the FROM clause does not. SQL-92 and later versions of SQL Server prefer the latter.

RowSearchCondition is used to filter a result set so that only rows that meet its conditions are acted upon. Typically rows are filtered by taking a column (possibly an expression) in each row, and comparing it with some other expression. RowSearchCondition has this syntax:

{[NOT] Predicate | RowSearchCondition}
[{AND | OR} [NOT] {Predicate | RowSearchCondition}]
...

Predicate is an expression that returns TRUE, FALSE, or UNKNOWN. Predicate has this syntax:

{
expression {= | <> | != | > | >= | !> | < | <= | !<} expression 
| string_expression [ NOT ] LIKE string_expression [ ESCAPE 'escape_character' ] 
| expression [ NOT ] BETWEEN expression AND expression 
| expression IS [ NOT ] NULL 
| CONTAINS( { column | * } , 'contains_search_condition' ) | FREETEXT ( { column | * } , 'freetext_string' ) 
| expression [ NOT ] IN ( subquery | expression [ ,...n ] ) 
| expression { = | < > | ! = | > | > = | ! > | < | < = | ! < } { ALL | SOME | ANY} ( subquery ) 
| EXISTS ( subquery ) 
}
Wildcards used by LIKE
% Any string of 0 or more characters _ Any single character
[   ] Any single character within:
  • the specified range
    EG: [a-c]
  • the specified set
    EG: [abc]
 [ ^ ] Any single character not within:
  • the specified range
    EG: [^a-c]
  • the specified set
    EG: [^abc]

A wild card at the beginning of an expression will not take advantage of an index.

EGs

WHERE in SELECT

The most common row filter is by comparing a column is with literals.

SELECT *
FROM tblA
WHERE Name = 'George'

WHERE in DELETE

The following two DELETE commands delete the same rows.

DELETE tblA
WHERE Price >= 10 AND Price <= 30
--The same thing, i.e. inclusive
DELETE tblA
WHERE Price BETWEEN 10 AND 20

WHERE in UPDATE

The following UPDATE commands change all data in the quantity column, for rows whose sold column is NULL.

UPDATE tblA
SET Quantity = 1
WHERE Sold IS NULL
UPDATE tblA
SET Quantity = 1
WHERE Sold = NULL --Some flavors of SQL either --don't allow this or  --must have an option set for it.

WHERE using NOT IN

The NOT IN comparison keywords sees if a column value is not in a list.

SELECT *
FROM tblA
WHERE Name NOT IN ('Fred', 'Barney', 'Wilma', 'Betty')
--The same thing
SELECT *
FROM tblA
WHERE Name <> ALL ('Fred', 'Barney', 'Wilma', 'Betty')

WHERE in sub-query

The IN comparison keyword is often used to check for rows who are turned up by a sub-query.

SELECT au_lname, au_fname
FROM authors
WHERE au_id IN
      (SELECT au_id
      FROM titleauthor
      WHERE royaltyper >50)
--The same thing
SELECT au_lname, au_fname
FROM authors
WHERE au_id = ANY
      (SELECT au_id
      FROM titleauthor
      WHERE royaltyper >50)
--The same thing
SELECT au_lname, au_fname
FROM authors
WHERE au_id = SOME
      (SELECT au_id
      FROM titleauthor
      WHERE royaltyper >50)

WHERE using ALL, SOME, or ANY

The ALL, SOME, and ANY comparison keywords modify the usual comparison operators.

--This is for all of the choices, i.e. the max of the subquery
SELECT Name FROM Product
WHERE Price > ALL
    (SELECT Price FROM AltProduct)
--This is for any one of the choices
SELECT Name FROM Product
WHERE Price > ANY
    (SELECT Price FROM AltProduct)

WHERE using LIKE

The LIKE comparison keyword can be used match rows where a column matches a pattern. This gets rid of rows with values like george, georgie, and georgia.

DELETE tblA
WHERE Name LIKE '[g]eorg%'

WHERE using LIKE and wildcard

The ESCAPE keyword is used because the character being searched for is itself a wildcard. Alternatively, the wild card may be enclosed in square brackets. Both of these examples look for records where the Name starts with my_.

UPDATE tblA
SET Name = 'X'
WHERE Name LIKE 'my/_' ESCAPE '/'
UPDATE tblA
SET Name = 'X'
WHERE Name LIKE 'my[_]'

WHERE using CONTAINS

The CONTAINS and FREETEXT keywords search character-based columns using fuzzy logic. CONTAINS has five basic options:

Page Modified: (Hand noted: 2007-10-12 20:06:54Z) (Auto noted: 2008-02-04 19:02:20Z)