Left Outer Join

select parentrequest.bankname, parentrequest.amount, parentrequest.bofa_ref#, parentrequest.gtemfilenum ' - ' parentrequest.sequencenumber as gtemfilenum, parentrequest.status, parentrequest.approved_bydate, parentrequest.maximum_maturitydate, childrequest.gtemfilenum ' - ' childrequest.sequencenumber as gtemfilenum, childrequeststatus.display as status, childrequest.creationdate, childrequest.maximummaturitydate as maximum_maturitydatefrom own_gtem.requestdetails childrequest join own_gtem.requeststatusdescription childrequeststatus on childrequest.requeststatuskey = childrequeststatus.requeststatuskey, ( select bd.bankname, rd.amount, rd.bacrefnum as bofa_ref#, rd.gtemfilenum, rd.sequencenumber, rsd.display as status, rd.approvedbydate as approved_bydate, rd.maximummaturitydate as maximum_maturitydate from own_gtem.requestdetails rd join own_gtem.requeststatusdescription rsd on rd.requeststatuskey = rsd.requeststatuskey left outer join own_gtem.bankfacilityconnection bfc on rd.requestdetailskey = bfc.requestdetailskey left outer join own_gtem.bankfacility bf on bfc.bankfacilitykey = bf.bankfacilitykey left outer join own_gtem.bankdetails bd on bf.bankdetailskey = bd.bankdetailskey where rd.requeststatuskey=9 ) parentrequestwhere parentrequest.gtemfilenum = childrequest.gtemfilenum and childrequest.requeststatuskey in (6, 7, 8)

Usage of Dual table

If the select query involves fetching data from multiple tables then we use dual table.

AND 0=(SELECT COUNT(*) FROM DUAL WHERE TBUONE.TBUNAME = 'TOC-NY' AND RTT.CODE = 'LACP-OAP' AND CD1.COUNTRYCODE = 'KR' AND RD.AUTOAPPROVEDFLAG = 'N' AND RD.AMOUNT < 500000)