Special Operators in SQL

The different special operators in SQL are as follows:

• ALL operator
• ANY Operator
• BETWEEN Operator
• EXISTS Operator
• IN Operator
• LIKE Operator

Now let us create a table to understand the examples of special operators:

<Employee>

<Dependents>

Details of all the special operators using the above tables are:

ALL operator

The ALL operator compares a value with all the values returned by the subquery and is true only if the given condition is satisfied for all the values. For example:

Select * from Employee
Where Emp_Salary > ALL (select Emp_Salary from Employee 
                                            where Emp_DeptID=30);

This query returns the details of all the employees whose salary is greater than the salary of all the employees in Department number 30 i.e. Angel in this case.

ANY operator

The ANY operator compares a value with all the values returned by the subquery and is true only if the given condition is satisfied for any value in the set of values. For example:

Select * from Employee
Where Emp_salary > ANY (select Emp_Salary from Employee 
                                             where Emp_DeptID=30);

This query returns the details of all the employees whose salary is greater than the salary of even one employee in Department number 30 i.e. Aaron, Harry, Angel and Will in this case.

BETWEEN operator

The BETWEEN operator returns the information within a given range of values, where the minimum and maximum of the range is specified. For example:

Select * from Employee
Where Emp_Salary BETWEEN 20000 AND 60000;

This query returns information about all the employees whose salary is between the range of 20000 and 60000 i.e. Angel and Will in this case.

EXISTS operator

The EXISTS operator only returns true if the subquery returns at least one record i.e if some data exists for the given subquery. For example:

Select * from Employee
Where EXISTS (select * from Dependents where
                          Employee.Emp_ID=dependents.Emp_ID);

This query will return results for only those employees who have any dependents i.e. Harry, Mary and Will.

IN operator

The IN operator is true if the query results in values that are contained in the list of constant values for the IN operator. For example:

Select * from Employee
Where Emp_ID IN (1,2,5);

This query gives details about the employees whose Employee number is 1,2 or 5 i.e Aaron, Harry and Will.

LIKE operator

The LIKE operator is used to select the values that match the patterns specified in the query. Two wildcard operators are used for this. For example:

Select * from Employee
Where Emp_Name LIKE “A%”

This query returns the data of all the employees whose name starts with A i.e. Aaron and Angel in our example.

Amit Diwan

Published on 03-Aug-2018 07:41:45