Oracle, LEFT OUTER JOIN not returning all rows from left table, instead behaving like INNER JOIN

Question

I'm doing a left outer join and only getting back matching rows like it was an inner join.

To simplify the data, my first table(ROW_SEG), or left table looks something like this:

ASN | DEPT NO
-----------------------
85  | 836
86  | null         
87  | null  

My second table(RF_MERCHANT_ORG) has DEPT_NAME, and some other things which i want to get when i have a dept number.

DEPT NO | DEPT_NAME
-----------------------
836     | some dept name 1
837     | some dept name 2
838     | some dept name 3

In this case after my join i'd only get 1 row, for ASN 85 that had a DEPT NO.

...omitting a bunch of SQL for simplicity

, ROW_SEG AS (
    SELECT *
    FROM VE_SI_EC_OI
    WHERE ROW_NUM BETWEEN 1 AND 1000 -- screen pagination, hardcoding values
)

-- ROW_SEG has a count of 1000 now

, RFS_JOIN AS (
    SELECT ROW_SEG.*
    ,MO.BYR_NO
    ,MO.BYR_NAME
    ,MO.DEPT_NAME
    FROM ROW_SEG
    LEFT OUTER JOIN RF_MERCHANT_ORG MO
    ON ROW_SEG.DEPT_NO = MO.DEPT_NO    
    WHERE MO.ORG_NO = 100  
)
SELECT * FROM RFS_JOIN; -- returns less than 1000

I only get back the number of rows equal to the number of rows that have dept nos. So in my little data example above i would only get 1 row for ASN 85, but i want all rows with BYR_NO, BYR_NAME, AND DEPT_NAME populated on rows where i had a DEPT_NO, and if not, then empty/null columns.

Answer 1

If ORG_NO is within the RF_MERCHANT_ORG table (using aliases consistently would help there) then acting like an inner join would then would be the correct result for the SQL being used.

The join should be this to make it act like a proper left join:

LEFT OUTER JOIN RF_MERCHANT_ORG MO ON ROW_SEG.DEPT_NO = MO.DEPT_NO AND MO.ORG_NO = 100

Answer 2

If ORG_NO is in RF_MERCHANGE_ORG, then that is likely to be the cause... the where condition is limiting the result set.