ORACLE EXISTS Condition

SQL: EXISTS Condition
The EXISTS condition is considered "to be met" if the subquery returns at least one row.

The syntax for the EXISTS condition is:

SELECT columns
FROM tables
WHERE EXISTS ( subquery );

The EXISTS condition can be used in any valid SQL statement - select, insert, update, or delete.

Example #1
Let's take a look at a simple example.  The following is an SQL statement that uses the EXISTS condition:

SELECT *
FROM suppliers
WHERE EXISTS
  (select *
    from orders
    where suppliers.supplier_id = orders.supplier_id);

This select statement will return all records from the suppliers table where there is at least one record in the orders table with the same supplier_id.

Example #2 - NOT EXISTS
The EXISTS condition can also be combined with the NOT operator.

For example,

SELECT *
FROM suppliers
WHERE not exists (select * from orders Where suppliers.supplier_id = orders.supplier_id);

This will return all records from the suppliers table where there are no records in the orders table for the given supplier_id.

Example #3 - DELETE Statement
The following is an example of a delete statement that utilizes the EXISTS condition:

DELETE FROM suppliers
WHERE EXISTS
  (select *
    from orders
    where suppliers.supplier_id = orders.supplier_id);

 

Example #4 - UPDATE Statement
The following is an example of an update statement that utilizes the EXISTS condition:

UPDATE supplier
SET supplier_name = ( SELECT customer.name
FROM customers
WHERE customers.customer_id = supplier.supplier_id)
WHERE EXISTS
  ( SELECT customer.name
    FROM customers
    WHERE customers.customer_id = supplier.supplier_id);

Example #5 - INSERT Statement
The following is an example of an insert statement that utilizes the EXISTS condition:

INSERT INTO supplier
(supplier_id, supplier_name)
SELECT account_no, name
FROM suppliers
WHERE exists (select * from orders Where suppliers.supplier_id = orders.supplier_id);

실무 적용했던 사례

((EXISTS ( SELECT  1
                      FROM SYS0060 T1,
                           SYS0020 T2
                    WHERE 1=1
                    AND ROLE_CD =  CD
                    AND CD_IDX = 'ROLE'
                    AND CD ='10'
                    AND USER_ID = #{user.USER_ID})) OR (T1.PJT_CD IN (SELECT DISTINCT PJT_CD FROM ITA0030 WHERE USER_ID = #{user.USER_ID})))