Left outer join

[iwan_santoso4]

Let say Table A has 2 columns and has the following value

Col1     Col2

ABC     123

DEF     345

GHI     678

Then Table B has 2 columns and has the following value

Col1     Col2

345     Main St.

768     Wall St.

as far as I understood left outer join will give me:

select a.col1, a.col2, b.col2 ad col3 from A as A left outer join B as B on a.Col2 = b.Col1;

Col1     Col2    Col3

ABC     123     ?

DEF     345     Main St.

GHI     678     Wall St.

However, I only get

Col1     Col2    Col3

DEF     345     Main St.

GHI     678     Wall St.

Which looks like a inner join. Please see the screen shot below for the actual data i'm using:

select

* from "_SYS_BIC"."com.isantoso.poc/SOURCE_DATA" where kunnr = '0000120207' order by matnr;

gives me:

Please note that on the second table, only material number starting with WDE exist.

select

a.mandt, a.kunnr, a.matnr, a.vkorg, a.vtweg, a.spart, a.pltyp, a.waers, a.werks,

                     knumh, kschl, kbetr, konwa, kpein, kmein 

from "_SYS_BIC"."com.isantoso.poc/SOURCE_DATA" as A left outer join

                          "_SYS_BIC"."com.isantoso.poc/ZPRO_UNION_TEST2" as B

                     on a.mandt = b.mandt

                     and a.vkorg = b.vkorg

                     and a.vtweg = b.vtweg

                     and a.kunnr = b.kunnr

                     and a.matnr = b.matnr

                     where a.mandt = '130'

                     and   b.kappl = 'V'

                     and   a.vkorg = '1000'

                     and   a.vtweg = '10'

                     and   b.kschl = 'ZPR0'

                     and   a.kunnr = '0000120207'

                     and   b.pltyp is null

                     and   b.waerk is null

                     and   b.matnr is not null

                     and   KFRST = ''

                     order by a.matnr;

gives me the following, which I thought all entry for material number not starting with WDE should also displayed with ? for the data from the second table

[iwan_santoso4 · Accepted solution]

never mind... found the issue in my sql statement:

and   b.kschl = 'ZPR0'

if there is no entry in the right table (2nd table) the value will be null and this condition remove all the entry with null... so I used:

and   ( b.kschl = 'ZPR0' or b.kschl is null )

That solved my problem.

[rama_shankar3 · Accepted solution]

Yes, that is correct.

Regards,

Rama