Geek Answers Handbook

Oracle, LEFT OUTER JOIN does not return all rows from the left table, but behaves like an INNER JOIN

I make a left outer join and get back the corresponding rows, such as inner join.

To simplify the data, my first table (ROW_SEG) or the 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 that I want to get when I have a department 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 joining, I would get only 1 row, for ASN 85, which had 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

, , dept nos. 1 ASN 85, , BYR_NO, BYR_NAME DEPT_NAME , DEPT_NO, , / .

+4
2

ORG_NO RF_MERCHANT_ORG ( ), , , SQL.

, :

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

ORG_NO RF_MERCHANGE_ORG, , , ... where .

+4

More articles:


All Articles