Special Operators in SQL

SQLSQLiteDatabase

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_IDEmp_NameEmp_SalaryEmp_DeptIDEmp_DeptName
1Aaron1000010Technical
2Harry1200020Operations
3Mary500030Finance
4Angel5500010Technical
5Will2000030Finance

<Dependents>

Dep_IDEmp_IDDep_NameDep_Age
10012Keith88
10023Kim5
10035Lucy90

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_IDEmp_NameEmp_SalaryEmp_DeptIDEmp_DeptName
4Angel5500010Technical

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_IDEmp_NameEmp_SalaryEmp_DeptIDEmp_DeptName
1Aaron1000010Technical
2Harry1200020Operations
4Angel5500010Technical
5Will2000030Finance

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_IDEmp_NameEmp_SalaryEmp_DeptIDEmp_DeptName
4Angel5500010Technical
5Will2000030Finance

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_IDEmp_NameEmp_SalaryEmp_DeptIDEmp_DeptName
2Harry1200020Operations
3Mary500030Finance
5Will2000030Finance

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_IDEmp_NameEmp_SalaryEmp_DeptIDEmp_DeptName
1Aaron1000010Technical
2Harry1200020Operations
5Will2000030Finance

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_IDEmp_NameEmp_SalaryEmp_DeptIDEmp_DeptName
1Aaron1000010Technical
4Angel5500010Technical
raja
Published on 03-Aug-2018 11:11:45
Advertisements