Loading
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

OperatorPurpose
LIKEPattern matching using wildcards ( %, _ )
INMatch values within a list
BETWEENMatch values within a range ( inclusive )
ALLCompare a value against all values returned by a subquery
ANYCompare a value against any ( at least one ) value from a subquery



1. LIKE Operator

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: 

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: 

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: 

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: 

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.