«Back to Home

Oracle Jump Start
Topics

How To Use Exists Condition In OraclePLSQL

Description

Exists condition is used in query and the query result depends on the row, which exists in a table. The sub query returns at least one row. It can be used in a select, insert, update or delete command.

Syntax
  1. Select *   
  2. from Table_name   
  3. where Exists (Subquery) ;
Example

The example, given below, uses select statement.
  1. Select *  
  2. from Employee  
  3. where Exists  
  4. (  
  5. select * from Departments  
  6. where Employee.Dept_no = Department.Dept_id  
  7. );  
1

Example

The example, given below, uses select statement with Not Exists.
  1. Select *  
  2. from Employee  
  3. where Not Exists  
  4. (  
  5. select * from Departments  
  6. where Employee.Dept_no = Department.Dept_id  
  7. );   
2 
 
Example

The example, given below, uses insert statement.
  1. insert into Supplier(supplier_id, supplier_name)  
  2. select Dept_no, Emp_name  
  3. from Employee  
  4. where exists  
  5. (  
  6. select * from Department  
  7. where Employee.Dept_no = department.Dept_id  
  8. );  
3
 
4 

Example

The example, given below, uses Update Statement.
  1. update CUSTOMER  
  2. set CUSTOMER_NAME = (Select Dept_name  
  3. from Department  
  4. where Department.dept_id = CUSTOMER.CUSTOMER_ID)  
  5. where Exists  
  6. (select Dept_name  
  7. from Department  
  8. where Department.dept_id = CUSTOMER.CUSTOMER_ID  
  9. );  
5
 
6 

Example

The example, given below, uses Delete statement.
  1. Delete from Customer  
  2. where Exists  
  3. (  
  4. select * from Department  
  5. where Customer.Customer_id = Department.Dept_id  
  6. );  
7
 
8

Summary

Thus, we learnt, Exists condition is used in query and the query result depends on the row, which exists in a table. We learnt, how to use this condition in Oracle with the examples.