Not all values getting listed from left join on condition-CodePudding

Can someone please help to understand why all values from left table are not getting listed. I have tried ANSI left join and outer join too.

I need cust_3 to be listed in final output too while using condition -

 b.cust_id != 'cust_3'

Possibly something trivial that I am not able to wrap my head around, will appreciate feedback.

Schema setup -

create table cust(cust_id varchar2(10));

create table prod(cust_id varchar2(10), prod_id varchar2(10));
insert into cust values('cust_1');
insert into cust values('cust_2');
insert into cust values('cust_3');
insert into cust values('cust_4');
insert into cust values('cust_5');

insert into prod values('cust_5','prod1');
insert into prod values('cust_5','prod2');
insert into prod values('cust_4','prod2');
insert into prod values('cust_4','prod1');
insert into prod values('cust_1','prod1');
insert into prod values('cust_1','prod2');
insert into prod values('cust_1','prod');
insert into prod values('cust_1','prod3');
insert into prod values('cust_2','prod3');
insert into prod values('cust_2','prod1');
insert into prod values('cust_2','prod4');
insert into prod values('cust_2','prod6');
insert into prod values('cust_2','prod8');
insert into prod values('cust_2','prod7');
insert into prod values('cust_2','prod9');

commit;


select a.cust_id,b.prod_id from cust a, prod b where a.cust_id=b.cust_id( ) and b.cust_id != 'cust_3';

CUST_ID    PROD_ID
---------- ----------
cust_5     prod1
cust_5     prod2
cust_4     prod2
cust_4     prod1
cust_1     prod1
cust_1     prod2
cust_1     prod
cust_1     prod3
cust_2     prod3
cust_2     prod1
cust_2     prod4
cust_2     prod6
cust_2     prod8
cust_2     prod7
cust_2     prod9

15 rows selected.

Output I am looking for -

CUST_ID    PROD_ID
---------- ----------
cust_5     prod1
cust_5     prod2
cust_4     prod2
cust_4     prod1
cust_1     prod1
cust_1     prod2
cust_1     prod
cust_1     prod3
cust_2     prod3
cust_2     prod1
cust_2     prod4
cust_2     prod6
cust_2     prod8
cust_2     prod7
cust_2     prod9
cust_3     NULL

CodePudding user response：

Your final filter condition b.cust_id != 'cust_3' means that it will only find rows where the condition is true and NULL (which the rows will be when the OUTER join does not find a match) is never not equal to anything so it effectively converts your query from a LEFT OUTER JOIN to an INNER JOIN.

If you want to fix it then add the filter to the left outer join condition:

select a.cust_id,
       b.prod_id
from   cust a,
       prod b
where  a.cust_id = b.cust_id( )
and    b.cust_id ( ) != 'cust_3';

And, better, use ANSI join syntax:

select a.cust_id,
       b.prod_id
from   cust a
       LEFT OUTER JOIN prod b
       ON (   a.cust_id =  b.cust_id 
          AND b.cust_id != 'cust_3' );

db<>fiddle here