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 CHAR and VARCHAR data 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”
  • _ matches exactly one character
    • e.g., LIKE 'L_t' matches “Lot” and “Lit” but not “Lt” and “Loot”
  • performs:
    • case-insensitive pattern matching by default
    • case-sensitive pattern matching if followed by the BINARY keyword
  • can escape the wildcard characters with \ (backslash)
    • LIKE 'a\%' matches a%

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.

  • DESC keyword orders rows in descending order
  • order by multiple columns by listing with commas

Example

SELECT * 
FROM CountryLanguage 
ORDER BY Language [DESC]; -- brackets are optional