Just a general explanation owuld be fine as to the possible reasoning this is occuring and some possible solutions.

SQL> select * from product;

PRODUCT_CD NAME PRODUCT_TY

---------- -------------------------------------------------- ----------

DATE_OFFE DATE_RETI

--------- ---------

CHK checking account ACCOUNT

01-JAN-00 01-JAN-30

SAV savings account ACCOUNT

01-JAN-00 01-JAN-30

MM money market account ACCOUNT

01-JAN-00 01-JAN-30

PRODUCT_CD NAME PRODUCT_TY

---------- -------------------------------------------------- ----------

DATE_OFFE DATE_RETI

--------- ---------

CD certificate of deposit ACCOUNT

01-JAN-00 01-JAN-30

MRT home mortgage LOAN

01-JAN-00

AUT auto loan LOAN

01-JAN-00

PRODUCT_CD NAME PRODUCT_TY

---------- -------------------------------------------------- ----------

DATE_OFFE DATE_RETI

--------- ---------

BUS business line of credit LOAN

01-JAN-00

SBL small business loan LOAN

01-JAN-00

8 rows selected.

ACCOUNT_ID PRODUCT_CD CUST_ID OPEN_DATE CLOSE_DAT LAST_ACTI STATUS

---------- ---------- ---------- --------- --------- --------- ----------

OPEN_BRANCH_ID OPEN_EMP_ID AVAIL_BALANCE PENDING_BALANCE

-------------- ----------- ------------- ---------------

1 CHK 1 15-JAN-00 04-JAN-05 ACTIVE

2 10 1057.75 1057.75

2 SAV 1 15-JAN-00 19-DEC-04 ACTIVE

2 10 500 500

3 CD 1 30-JUN-04 30-JUN-04 ACTIVE

2 10 3000 3000

ACCOUNT_ID PRODUCT_CD CUST_ID OPEN_DATE CLOSE_DAT LAST_ACTI STATUS

---------- ---------- ---------- --------- --------- --------- ----------

OPEN_BRANCH_ID OPEN_EMP_ID AVAIL_BALANCE PENDING_BALANCE

-------------- ----------- ------------- ---------------

4 CHK 2 12-MAR-01 27-DEC-04 ACTIVE

2 10 2258.02 2258.02

5 SAV 2 12-MAR-01 11-DEC-04 ACTIVE

2 10 200 200

6 CHK 3 23-NOV-02 30-NOV-04 ACTIVE

3 13 1057.75 1057.75

ACCOUNT_ID PRODUCT_CD CUST_ID OPEN_DATE CLOSE_DAT LAST_ACTI STATUS

---------- ---------- ---------- --------- --------- --------- ----------

OPEN_BRANCH_ID OPEN_EMP_ID AVAIL_BALANCE PENDING_BALANCE

-------------- ----------- ------------- ---------------

7 MM 3 15-DEC-02 05-DEC-04 ACTIVE

3 13 2212.5 2212.5

8 CHK 4 12-SEP-03 03-JAN-05 ACTIVE

1 1 534.12 534.12

9 SAV 4 15-JAN-00 24-OCT-04 ACTIVE

1 1 767.77 767.77

ACCOUNT_ID PRODUCT_CD CUST_ID OPEN_DATE CLOSE_DAT LAST_ACTI STATUS

---------- ---------- ---------- --------- --------- --------- ----------

OPEN_BRANCH_ID OPEN_EMP_ID AVAIL_BALANCE PENDING_BALANCE

-------------- ----------- ------------- ---------------

10 MM 4 30-SEP-04 11-NOV-04 ACTIVE

1 1 5487.09 5487.09

11 CHK 5 27-JAN-04 05-JAN-05 ACTIVE

4 16 2237.97 2897.97

12 CHK 6 24-AUG-02 29-NOV-04 ACTIVE

1 1 122.37 122.37

ACCOUNT_ID PRODUCT_CD CUST_ID OPEN_DATE CLOSE_DAT LAST_ACTI STATUS

---------- ---------- ---------- --------- --------- --------- ----------

OPEN_BRANCH_ID OPEN_EMP_ID AVAIL_BALANCE PENDING_BALANCE

-------------- ----------- ------------- ---------------

13 CD 6 28-DEC-04 28-DEC-04 ACTIVE

1 1 10000 10000

14 CD 7 12-JAN-04 12-JAN-04 ACTIVE

2 10 5000 5000

15 CHK 8 23-MAY-01 03-JAN-05 ACTIVE

4 16 3487.19 3487.19

ACCOUNT_ID PRODUCT_CD CUST_ID OPEN_DATE CLOSE_DAT LAST_ACTI STATUS

---------- ---------- ---------- --------- --------- --------- ----------

OPEN_BRANCH_ID OPEN_EMP_ID AVAIL_BALANCE PENDING_BALANCE

-------------- ----------- ------------- ---------------

16 SAV 8 23-MAY-01 12-OCT-04 ACTIVE

4 16 387.99 387.99

17 CHK 9 30-JUL-03 15-DEC-04 ACTIVE

1 1 125.67 125.67

18 MM 9 28-OCT-04 28-OCT-04 ACTIVE

1 1 9345.55 9845.55

ACCOUNT_ID PRODUCT_CD CUST_ID OPEN_DATE CLOSE_DAT LAST_ACTI STATUS

---------- ---------- ---------- --------- --------- --------- ----------

OPEN_BRANCH_ID OPEN_EMP_ID AVAIL_BALANCE PENDING_BALANCE

-------------- ----------- ------------- ---------------

19 MM 9 30-JUN-04 30-JUN-04 ACTIVE

1 1 1500 1500

20 MM 10 30-SEP-02 15-DEC-04 ACTIVE

4 16 23575.12 23575.12

21 MM 10 01-OCT-02 28-AUG-04 ACTIVE

4 16 0 0

ACCOUNT_ID PRODUCT_CD CUST_ID OPEN_DATE CLOSE_DAT LAST_ACTI STATUS

---------- ---------- ---------- --------- --------- --------- ----------

OPEN_BRANCH_ID OPEN_EMP_ID AVAIL_BALANCE PENDING_BALANCE

-------------- ----------- ------------- ---------------

22 MM 11 22-MAR-04 14-NOV-04 ACTIVE

2 10 9345.55 9345.55

23 MM 12 30-JUL-03 15-DEC-04 ACTIVE

4 16 38552.05 38552.05

24 CHK 13 22-FEB-04 17-DEC-04 ACTIVE

3 3 50000 50000

ACCOUNT_ID PRODUCT_CD CUST_ID OPEN_DATE CLOSE_DAT LAST_ACTI STATUS

---------- ---------- ---------- --------- --------- --------- ----------

OPEN_BRANCH_ID OPEN_EMP_ID AVAIL_BALANCE PENDING_BALANCE

-------------- ----------- ------------- ---------------

35 SBL 1 23-APR-17

55 SBL 9 23-APR-12

4

56 LOAN 8 23-APR-12

SQL> SELECT * FROM BRANCH;

BRANCH_ID NAME ADDRESS

---------- -------------------- ------------------------------

CITY ST ZIP

-------------------- -- ------------

1 Headquarters 3882 Main St.

Waltham MA 02451

2 Woburn Branch 422 Maple St.

Woburn MA 01801

3 Quincy Branch 125 Presidential Way

Quincy MA 02169

BRANCH_ID NAME ADDRESS

---------- -------------------- ------------------------------

CITY ST ZIP

-------------------- -- ------------

4 So. NH Branch 378 Maynard Ln.

Salem NH 03079

SELECT b.branch_id, loan.s LOAN, acc.s ACCOUNT

FROM branch b LEFT OUTER JOIN (SELECT a.open_branch_id, sum(a.avail_balance) s

FROM account a INNER JOIN product p

ON a.product_cd = p.product_cd

WHERE p.product_type_cd='LOAN'

GROUP BY a.open_branch_id) loan

ON b.branch_id = loan.open_branch_id

LEFT OUTER JOIN (SELECT a.open_branch_id, sum(a.avail_balance) s

FROM account a INNER JOIN product p

ON a.product_cd = p.product_cd

WHERE p.product_type_cd='ACCOUNT'

GROUP BY a.open_branch_id) acc

ON b.branch_id = acc.open_branch_id

ORDER BY b.branch_id;

The output has only NULLs in the LOAN column. This seems inconsistent with LEFT OUTER JOIN. Provide a coherent explanation, including insertion of some data that would show in some row of the LOAN column.