Oracle PL/SQL SubQuery example

The following subquery uses EXISTS method to check whether the employee work in UK site. The crucial part of the query is the use of the usr.idemployee (main query) to filter the data in the subquery.

The subquery could be selecting from any tables but the query result is appended to the main query. The subquery result doesn't have to exist in any table.


select usr.iduser, usr.name,
DECODE ((SELECT 1
FROM DUAL
WHERE EXISTS (
SELECT dummy
FROM empworkplace wrk
WHERE usr.idemployee = wrk.idemployee
AND wrk.sitekey = 'UK'),
NULL, 0,
1
) AS fromUKSite,
from users usr.