Convert nested select to join-CodePudding

SELECT bank.bank_name, 
   (SELECT COALESCE(SUM(account.balance),0) 
   FROM account 
   WHERE account.bank_name = bank.bank_name 
       AND account.account_type = 'loan') AS total 
FROM bank

Account Table:

CodePudding user response：

Your query is probably more efficient than a join/group by version, but it is equivalent to:

SELECT b.bank_name, COALESCE(SUM(a.balance), 0) as total 
FROM bank b LEFT JOIN
     account a
     ON a.bank_name = b.bank_name AND a.account_type = 'loan'
GROUP BY b.bank_name;

I should note that if all banks have loans (which seems reasonable) no JOIN is necessary:

SELECT a.bank_name, SUM(a.balance as total 
FROM account a
WHERE a.account_type = 'loan'
GROUP BY a.bank_name;