Difference noted in the way LEFT OUTER JOIN and INNER JOIN retrieves data for my query

Database
Enthusiast

Difference noted in the way LEFT OUTER JOIN and INNER JOIN retrieves data for my query

Hi All,

I have a view with 4 columns defined in it and the definition of the view is as below:

replace view viewdb.vw1 as (

select t1.*, ( select max(t2.col1)

              from db.table2 t2

                      where t1.col2 is null

                        and lower(t1.col3)  (CASESPECIFIC)   like lower(t2.col1||'%')  (CASESPECIFIC)

                        and lower(t2.col1)  (CASESPECIFIC)  not in ('xxxxx', 'yyyyy')) as newcol,

       case when t1.col2 is not null then lower(t1.col2)

            when newcol is not null then lower(newcol)

            when lower(t1.col3) (CASESPECIFIC)  like any ('abc%','bca%','xyx%') then 'aayyzz'

       end as col4

 from db.table1 t1

 );

Assume db.table1 has 3 columns (col1, col2, col3).

Now i am trying to perform the below query where Mtable has 1000 records:

select t1.*, v2.col2, v2.col4

   from db.MTable t1

left outer join viewdb.vw1 v2 on (t1.col1= v2.col1)

Assumption is that t1.col1 does exist in v2 view and hence the join based on this column will have successful rows.

As part of the left outer join, it is expected that v2.col2 and v2.col4 value should have not null values but it is noted that v2.col4 has NULL values even when v2.col2 has a not null value.

Expected values are noted if we perform the above join as INNER JOIN.

Output rows using both the joins is noted to be 1000 rows (same as MTable count).

I am trying to understand why the v2.col4 values are showing NULLs while it actually has values when i am performing LEFT OUTER JOIN. Please help me understand the difference noted here.

I am clear on how LEFT OUTER JOIN and INNER JOIN works. But the above quer LEFT OUTER JOIN results are showing NULLs values to columns that actually have values. Please explain.

1 REPLY
Enthusiast

Re: Difference noted in the way LEFT OUTER JOIN and INNER JOIN retrieves data for my query

Any suggestions on this please.