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

It is common to use BETWEEN for dates. In T-SQL it is inclusive but may be exclusive in other variants of SQL. 

DELETE tblB
WHERE Started_on between '2011-02-18' and '2011-02-19'

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)

--The same thing
SELECT au_lname, au_fname
FROM authors
WHERE EXISTS
      (SELECT *
      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:

WHERE using EXISTS

EXISTS specifies a subquery to test for the existence of rows.

SELECT FullName
FROM Users
WHERE EXISTS (SELECT NULL)
ORDER BY 1;

Page Modified: (Hand noted: 2007-10-12 20:06:54Z) (Auto noted: 2011-12-19 22:48:20Z)