«Back to Home

Oracle Jump Start
Topics

How To Use Special Operator In Oracle

Description
 
Some special operators used in subqueries are-
 
   • Exists Operator
   • Any operator
   • Some Operator
   • All operator
 
Exists Operator
 
Exists operator is frequently used with the correlated subqueries. If the value exists, it returns TRUE; if it does not exists, it returns false.
Not Exists operator is more reliable, if the subquery returns any null values.
   • This operator is used to check for the existence of values.
   • This operator produces a Boolean result.
   • It takes a sub query as the arguments and evaluates it to true, if the sub query produces any output and false, if the subquery does not produce any output.
 
Example
  1. SELECT * FROM employee  
  2. WHERE EXISTS (SELECT * FROM departments  
  3. WHERE employee.emp_id = departments.Emp_id);  
1 
 
Any Operator
 
   • Used along with the relational operators.
   • Similar to IN Operator, not only used in subqueries.
   • “some” and “any” operator can be used interchangeably.
 
The “any” operator compares the lowest value from the set.
List the employee names, whose salary is less than the lowest salary of an employee belonging to dept_no 102.
 
Example
  1. Select Emp_name from employee  
  2. where salary < any (select salary from employee where dept_no = 102);  
2 
 
Some Operator
 
“Some” and “any” operator can be used interchangeably.
  1. Select Emp_name from employee  
  2. where salary < Some (select salary from employee where dept_no = 102);  
3 
 
All Operator
 
In “All” operator, the predicate is true, if every value selected by the subquery satisfies the condition in the predicate of the outer query.
 
Example
  1. Select Emp_name from employee  
  2. where salary > ALL (select salary from employee where dept_no = 107);  
4 
 
Summary
 
Thus, we learnt, special operators are used in subqueries. We learnt, how to use these special operators with the examples.