Special Operators
In addition to standard logical operators like AND, OR, and NOT, SQL offers special operators that make queries more powerful, readable, and efficient. These operators help you search for patterns, match multiple values, filter by ranges, and compare against subqueries.
List of Special Operators in SQL
| Operator | Purpose |
|---|---|
| LIKE | Pattern matching using wildcards ( %, _ ) |
| IN | Match values within a list |
| BETWEEN | Match values within a range ( inclusive ) |
| ALL | Compare a value against all values returned by a subquery |
| ANY | Compare a value against any ( at least one ) value from a subquery |
1. LIKE Operator
Used for pattern matching in strings. Works with:
Example:
Used for pattern matching in strings. Works with:
- % - any sequence of characters
- _ - exactly one character
Example:
SELECT * FROM customers
WHERE name LIKE 'Joh%';
Finds all names starting with "Joh" (e.g., John, Johnson, Johanna).
2. IN Operator
Used to match values from a list.
Example:
Used to match values from a list.
Example:
SELECT * FROM employees
WHERE department IN ('HR', 'IT', 'Finance');Equivalent to: department = 'HR' OR department = 'IT' OR department = 'Finance'.
3. BETWEEN Operator
Filters values within a specified range (inclusive).
Example:
Filters values within a specified range (inclusive).
Example:
SELECT * FROM orders
WHERE amount BETWEEN 500 AND 1000;Fetches orders where the amount is between 500 and 1000.
4. ALL Operator
Compares a value against all values returned by a subquery. Condition must be true for all values.
Example:
Compares a value against all values returned by a subquery. Condition must be true for all values.
Example:
SELECT name FROM students
WHERE marks > ALL (SELECT marks FROM students WHERE class = 'B');
Fetches students whose marks are higher than all students in Class B.
5. ANY Operator
Compares a value against any value in a subquery. Returns TRUE if the condition matches at least one.
Example:
Compares a value against any value in a subquery. Returns TRUE if the condition matches at least one.
Example:
SELECT name FROM students
WHERE marks > ANY (SELECT marks FROM students WHERE class = 'B');
Fetches students whose marks are greater than at least one student in Class B.
Key Point
- LIKE is great for searching text patterns.
- IN helps avoid long OR conditions.
- BETWEEN simplifies range filtering.
- ALL and ANY make subquery comparisons more flexible.
These special operators give you the power to write efficient and elegant SQL queries for real-world scenarios.