Subqueries are used to get values from multiple tables in a single query, based on conditions. We can also combine these subqueries with case statements like the exmaple below.
Example
SELECT bank_name,beneficiary_name,account_no,bank_code,identifier,submerchant_ref,case when submerchant_ref IS NULL then (SELECT reg_no FROM merchant WHERE identifier=m.identifier) ELSE (SELECT business_reg_id FROM submerchant WHERE submerchant_ref=m.submerchant_ref) end FROM merchant_bank_details m WHERE STATUS=1