Operator

Description

=	Returns True if both the left and right expressions are equal.
<>	Returns True if both the left and right expressions are not equal.
>	Returns True if the left expression is greater than the right expression.
>=	Returns True if the left expression is greater than or equal to the right expression.
<	Returns True if the left expression is less than the right expression.
<=	Returns True if the left expression is less than or equal to the right expression.
LIKE	Returns True if the left string expression matches the right pattern expression. The percent (%) character can be used to represent multiple unknown characters in the pattern expression, while the underline (_) character can be used to represent a single unknown character in the pattern expression. In addition, the ESCAPE clause can be used after the pattern expression to specify a single character to be used as an escape character in front of any percent or underline literal characters in the pattern expression itself. This is useful when you wish to treat the percent or underline characters as literal characters for the purposes of the comparison.
NOT LIKE	Returns True if the left string expression does not match the right pattern expression. It is the inverse of the LIKE operator.  Both LIKE and NOT LIKE can correctly handle situations where a specific collation treats two characters as collating as one character, such as is the case with 'ss' in the German collations.
BETWEEN	Returns True if the left expression is between the two right expressions. The right expressions must be separated by the AND keyword.
NOT BETWEEN	Returns True if the left expression is not between the two right expressions. It is the inverse of the BETWEEN operator.
IN	Returns True if the left expression equals one of the right expressions. The right expressions are specified as a comma-delimited list of expressions enclosed in parentheses.
NOT IN	Returns True if the left expression does not equal one of the right expressions. It is the inverse of the IN operator.
IS NULL	Returns True if the left expression is null.
IS NOT NULL	Returns True if the left expression is not null. It is the inverse of the IS NULL operator.

-- The following uses the LIKE operator
-- to search the Notes column for the
-- occurrence of the string 'angry'

SELECT *
FROM customers
WHERE Notes LIKE '%angry%'

-- This is the same as the last search
-- except this search has been modified
-- to use a case-insensitive comparison
-- using the COLLATE clause

SELECT *
FROM customers
WHERE Notes COLLATE ANSI_CI LIKE '%angry%'

-- The following uses the LIKE operator's
-- ESCAPE clause to perform a search on a
-- phrase that includes the percent (%)
-- character

SELECT *
FROM Orders
WHERE AdjustmentNotes LIKE '20/%' ESCAPE '/'

-- The following query searches for all
-- orders placed between January 1st and
-- January 31st of 2007

SELECT *
FROM Orders
WHERE OrderDate BETWEEN DATE '2007-01-01' AND
DATE '2007-01-31'

-- The following query searches for all
-- orders placed by someone in the states
-- of California (CA) or Nevada (NV)

SELECT *
FROM Orders
WHERE State IN ('CA','NV')