Left Join check for Nulls not working

Database
Enthusiast

Left Join check for Nulls not working

I am using Teradata version 14.00.07.12 . Checking for nulls in left join does not seem to be working. When I create the following:

create TABLE tblA ( cola int, colb int, colc int);

create TABLE tblB ( cola int, colb int);

And run this query: 

With cte_vars as

(

    Select 'tbla' Table1,

        'tblb' Table2,

        'dev_data' DB

)

Select 'Columns in '|| var.Table1 || ' not in ' || var.Table2 || ':' From cte_vars var

Union

Select  tbl1.ColumnName From  DBC.ColumnsV tbl1

Join cte_vars cte On tbl1.TableName = cte.Table1

Left Join DBC.ColumnsV tbl2 On  tbl1.ColumnName = tbl2.ColumnName

Where tbl1.DatabaseName = cte.DB And   tbl2.TableName = cte.Table2  And tbl2.DatabaseName = cte.DB 

And tbl2.DatabaseName Is Null

Union

Select 'Columns in '|| var.Table2 || ' not in ' || var.Table1 || ':' From cte_vars var

Union

Select  tbl2.ColumnName From  DBC.ColumnsV tbl2

Join cte_vars cte On tbl2.TableName = cte.Table2

Left Join DBC.ColumnsV tbl1 On tbl1.ColumnName = tbl2.ColumnName  

Where tbl2.DatabaseName = cte.DB And tbl1.TableName = cte.Table1  And tbl1.DatabaseName = cte.DB 

And tbl1.ColumnName Is Null

No rows are returned( colc should be returned)

When I run this query: 

With cte_vars as

(

    Select 'tbla' Table1,

        'tblb' Table2,

        'dev_data' DB

)

Select Chr(17) || 'Columns in '|| var.Table1 || ' not in ' || var.Table2 || ':' From cte_vars var

Union

Select distinct Chr(18) || ColumnName from DBC.ColumnsV tbl1 Join cte_vars cte On tbl1.TableName = cte.Table1  And tbl1.DatabaseName = cte.DB

Except

Select distinct Chr(18) || ColumnName from DBC.ColumnsV tbl2 Join cte_vars cte On tbl2.TableName = cte.Table2 And tbl2.DatabaseName = cte.DB

Union

Select Chr(19) || 'Columns in '|| var.Table2 || ' not in ' || var.Table1 || ':' From cte_vars var

Union

Select distinct Chr(20) || ColumnName from DBC.ColumnsV tbl2 Join cte_vars cte On tbl2.TableName = cte.Table2 And tbl2.DatabaseName = cte.DB

Except

Select distinct Chr(20) || ColumnName from DBC.ColumnsV tbl1 Join cte_vars cte On tbl1.TableName = cte.Table1  And tbl1.DatabaseName = cte.DB

Colc is returned. I would really like to be able to check for nulls in a left join.

3 REPLIES
Teradata Employee

Re: Left Join check for Nulls not working

Putting filter conditions on the inner table (e.g. "And tbl2.TableName = cte.Table2 And tbl2.DatabaseName = cte.DB" in the first subquery) into the WHERE clause effectively makes it an INNER join. These should be in the ON clause instead.

You could also modify the WHERE clause to allow for NULLs in these other columns for nonmatching rows, but the resulting plan might be less efficient.

Enthusiast

Re: Left Join check for Nulls not working

Thanks, that resovled the issue! Teradata is quite a different animal than MS SQL server.

Junior Contributor

Re: Left Join check for Nulls not working

This is not a Teradata-specific issue, SQL Server will return exactly the same result, as it's based on Standard SQL :)

You'll find a nice case study on the where-to-place-conditions topic in the manuals:

http://www.info.teradata.com/htmlpubs/DB_TTU_15_00/index.html#page/SQL_Reference/B035_1146_015K/ch02...