Special Operators and Clauses in SQL
IN Operator
The IN operator is used in a WHERE clause to determine if a value matches one of several values.
Example
SELECT * FROM CountryLanguage WHERE Language IN ('Dutch', 'Kongo', 'Albanian');
BETWEEN Operator
The BETWEEN operator provides an alternative way to determine if a value is between two other values.
- Syntax:
value BETWEEN minValue AND maxValue - is equivalent to
value >= minValue AND value <= maxValue - is valid for
CHARandVARCHARdata types
LIKE Operator
The LIKE operator, when used in a WHERE clause, matches text against a pattern using the two wildcard characters % and _.
%matches any number of characters- e.g.,
LIKE 'L%t'matches “Lt”, “Lot”, “Lift”, and “Lol cat”
- e.g.,
_matches exactly one character- e.g.,
LIKE 'L_t'matches “Lot” and “Lit” but not “Lt” and “Loot”
- e.g.,
- performs:
- case-insensitive pattern matching by default
- case-sensitive pattern matching if followed by the
BINARYkeyword
- can escape the wildcard characters with
\(backslash)LIKE 'a\%'matchesa%
DISTINCT Clause
The DISTINCT keyword is used with a SELECT statement to return only unique or ‘distinct’ values.
Example
SELECT DISTINCT Language FROM CountryLanguage WHERE IsOfficial = 'F';
ORDER BY Clause
The ORDER BY clause orders selected rows by one or more columns in ascending (alphabetic or increasing) order.
DESCkeyword orders rows in descending order- order by multiple columns by listing with commas
Example
SELECT * FROM CountryLanguage ORDER BY Language [DESC]; -- brackets are optional