Left Join in Oracle to Join columns with Null Value-CodePudding

I have a table like below :

Table_a :

Pos_id    col1     col2   value
12221     null     null    Car
12222     112      1111    Bike
12222     112      1112    Bus
12222     112      1113    Roller

Table_b :

pos_id  col1       col2    line_nr
12221     100      1000     1
12222     112      1111     1
12222     112      1112     2
12222     112      1113     3

I want to select the values of table_a whose line_nr is 1 in the table_b.

so I have tried LEFT JOIN.

select * from table_a
left join table_b
on a.pos_id = b.pos_id
and a.col1 = b.col1
and a.col2 = b.col2
where b.line_nr = 1;

So this doesnt select the column that has null in the table_a.

I need to select the below given columns from table_a

Pos_id    col1     col2   value
12221     null     null    Car
12222     112      1111    Bike

CodePudding user response：

You must use left outer join and the accept row with line_nr = 1 or NULL

SELECT a.POS_ID, a.COL1, a.COL2, a.VAL
FROM table_a a
LEFT OUTER JOIN table_b b 
ON a.pos_id = b.pos_id
AND a.col1 = b.col1
AND a.col2 = b.col2
where nvl(b.line_nr,1) = 1
;

    POS_ID       COL1       COL2 VAL   
---------- ---------- ---------- ------
     12222        112       1111 Bike  
     12221                       Car

This is probably what you mean - get rows from a that either match the b and have line_nr = 1 or do not match (i.e. line_nr is null)