账户余额函数未返回空行：如何显示无账户用户的记录？

当前系统中，调用get_customer_balance函数可返回全部4名客户的记录，无交易的客户Y331964（Sandy）返回NULL，符合预期。但执行账户余额查询时，仅显示有账户的客户记录，无账户的Sandy完全不出现，需保留无交易账户返回NULL的逻辑。

问题根源 #

账户余额查询使用INNER JOIN关联CUSTOMER_ACCOUNTS和CUSTOMERS：

FROM   CUSTOMER_ACCOUNTS CA
       INNER JOIN customers c
       ON ca.customer_id = c.customer_id;

INNER JOIN仅返回两张表都有匹配项的记录，而Sandy在CUSTOMER_ACCOUNTS中无任何账户记录，因此被过滤，无法显示。

修复方案 #

1. 修改账户余额查询语句

将关联方式改为LEFT JOIN，以CUSTOMERS为主表，确保所有客户都能被返回，无账户的客户对应账户字段显示NULL：

SELECT 
       C.CUSTOMER_ID,
       C.FIRST_NAME,
       C.LAST_NAME, 
       CA.ACCOUNT_NUMBER,
       CA.IS_ACTIVE,
       get_account_balance(ca.account_number) AS balance
FROM   customers c
       LEFT JOIN CUSTOMER_ACCOUNTS CA
       ON c.customer_id = ca.customer_id;

2. 优化get_account_balance函数（可选）

原函数传入NULL时会返回所有交易的总余额，不符合“无账户时返回NULL”的预期，可修改逻辑：

CREATE OR REPLACE FUNCTION get_account_balance(
  i_account_number IN TRANSACTIONS.ACCOUNT_NUMBER%TYPE
) RETURN TRANSACTIONS.TRANSACTION_AMOUNT%TYPE
IS
  v_balance TRANSACTIONS.TRANSACTION_AMOUNT%TYPE;
BEGIN
  -- 传入账户号为NULL时直接返回NULL
  IF i_account_number IS NULL THEN
    RETURN NULL;
  END IF;
  
  SELECT SUM(
           CASE transaction_type WHEN 'C' THEN -1 ELSE 1 END 
           * transaction_amount
         )
  INTO   v_balance
  FROM   transactions
  WHERE  account_number = i_account_number;
  
  RETURN v_balance;
END;
/

3. 优化get_customer_balance函数（可选）

原函数中OR ca.customer_id IS NULL条件多余（customer_accounts的customer_id是外键，不会为NULL），改为LEFT JOIN可更严谨处理有账户无交易的场景：

CREATE OR REPLACE FUNCTION get_customer_balance(
  i_customer_id IN customers.customer_id%TYPE
) RETURN transactions.transaction_amount%TYPE
IS
  v_balance transactions.transaction_amount%TYPE;
BEGIN
  SELECT SUM (
           CASE t.transaction_type
             WHEN 'C' THEN -t.transaction_amount
             ELSE t.transaction_amount
           END 
         )
  INTO v_balance
  FROM customer_accounts ca
  LEFT JOIN transactions t ON t.account_number = ca.account_number
  WHERE ca.customer_id = i_customer_id;

  RETURN v_balance;
END get_customer_balance;
/

验证结果 #

修改后的查询会返回全部4名客户：

• Bonnie、Lisa、Barbara的账户记录正常显示，无交易账户返回NULL；
• Sandy的账户编号、状态显示NULL，balance字段也返回NULL，符合预期。

内容的提问来源于stack exchange，提问作者Beefstu