hades hades - 5 months ago 19
SQL Question

Oracle SQL Outer Left Join not returning rows

I am trying to map 3 tables in oracle database:

testHdr, testDet, testOther

testHdr is the header table, testDet is the detail table, and testOther is just the supplementary table that i need to get few columns from.

It is possible that there are data in testHdr but not in testDet. I want to show either empty/null value from testDet if there is no value.

Key to map testHdr to testDet is adj_no and adj_no2

SELECT hdr.*, det.*, othr.*
FROM testHdr hdr, testDet det, testOther othr
where hdr.adj_no = det.adj_no (+)
and hdr.adj_no2 = det.adj_no2 (+)
and i.key3 = det.key3
and hdr.adj_no = '112'

*112 has only header record but no detail record

  • I wish to get data for hdr.adj_no = 112, if there are header data like 113, 114, etc, it won't show in the query result.

I tried the above query with outer leftjoin but it shows:

ORA-01416: two tables cannot be outer-joined to each other


You should use an explicit left join:

SELECT hdr.*, det.*, othr.*  
FROM testHdr hdr
LEFT JOIN testDet det
    ON hdr.adj_no = det.adj_no AND
       hdr.adj_no2 = det.adj_no2
LEFT JOIN testOther othr
--  ON <some condition>
WHERE i.key3 = det.key3 AND
      hdr.adj_no = '112'

In your original query you never specified any join conditions for the testOther table, which means you are doing a cross join of that table. While this should not affect your actual problem, if the cross join were not intended, you should add an ON clause restricting this join.

One reason why most of the database world has moved away from the implicit join syntax you used is precisely because it makes it hard to see what is being joined, and it also makes it easier to miss a join condition.