Special Operators

SQL provides several special operators that allow for more complex comparisons and data filtering. These operators are particularly useful for handling ranges, lists, patterns, and NULL values.

List of Special Operators

Operator	Description
BETWEEN	Checks if a value is within a range
IN	Checks if a value matches any value in a list
LIKE	Searches for a specified pattern in a value
IS NULL	Checks for NULL values
EXISTS	Checks for the existence of rows that satisfy a subquery

Examples

BETWEEN Operator

SELECT * FROM products 
WHERE price BETWEEN 10 AND 20;

This query selects all products with a price between 10 and 20 (inclusive).

IN Operator

SELECT * FROM employees 
WHERE department IN ('Sales', 'Marketing', 'IT');

This query selects all employees in the Sales, Marketing, or IT departments.

LIKE Operator

SELECT * FROM customers 
WHERE email LIKE '%@gmail.com';

This query selects all customers with a Gmail email address.

Common wildcards used with LIKE:

• % : Represents zero or more characters
• _ : Represents a single character

IS NULL Operator

SELECT * FROM orders 
WHERE shipping_date IS NULL;

This query selects all orders that haven't been shipped yet.

EXISTS Operator

SELECT * FROM suppliers 
WHERE EXISTS (SELECT 1 FROM products 
              WHERE products.supplier_id = suppliers.id 
              AND products.units_in_stock = 0);

This query selects all suppliers who have at least one product out of stock.

Combining Special Operators

SELECT * FROM employees 
WHERE (salary BETWEEN 50000 AND 100000)
  AND (department IN ('Sales', 'Marketing'))
  AND (email LIKE '%@company.com')
  AND (termination_date IS NULL);

This query combines multiple special operators to select current employees in Sales or Marketing with a specific salary range and company email.

These special operators provide powerful ways to filter and select data in SQL queries. They're often used in combination with other SQL clauses and operators to create complex and precise data retrieval operations.