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>

Emp_ID Emp_Name Emp_Salary Emp_DeptID Emp_DeptName
1 Aaron 10000 10 Technical
2 Harry 12000 20 Operations
3 Mary 5000 30 Finance
4 Angel 55000 10 Technical
5 Will 20000 30 Finance

<Dependents>

Dep_ID Emp_ID Dep_Name Dep_Age
1001 2 Keith 88
1002 3 Kim 5
1003 5 Lucy 90

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.

Emp_ID Emp_Name Emp_Salary Emp_DeptID Emp_DeptName
4 Angel 55000 10 Technical

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.

Emp_ID Emp_Name Emp_Salary Emp_DeptID Emp_DeptName
1 Aaron 10000 10 Technical
2 Harry 12000 20 Operations
4 Angel 55000 10 Technical
5 Will 20000 30 Finance

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.

Emp_ID Emp_Name Emp_Salary Emp_DeptID Emp_DeptName
4 Angel 55000 10 Technical
5 Will 20000 30 Finance

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.

Emp_ID Emp_Name Emp_Salary Emp_DeptID Emp_DeptName
2 Harry 12000 20 Operations
3 Mary 5000 30 Finance
5 Will 20000 30 Finance

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.

Emp_ID Emp_Name Emp_Salary Emp_DeptID Emp_DeptName
1 Aaron 10000 10 Technical
2 Harry 12000 20 Operations
5 Will 20000 30 Finance

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.

Emp_ID Emp_Name Emp_Salary Emp_DeptID Emp_DeptName
1 Aaron 10000 10 Technical
4 Angel 55000 10 Technical

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