NULL handling in NOT IN clause - Scenario to handle NULL in Teradata

Very often, we try to find out the values from a table (employee) where the employee_ID is not in the table Resigned. 

One way of achieving that is to use a NOT IN clause. But, beware, since presence of NULL may give you the wrong information.

NULL in not exists results in no rows returned, because no NULL in Teradata is equal.
Moreover, any operation on NULL returns a NULL.

So, make sure to modify your inserts using the Coalesce(EMP_Salary, 0) for all retired employees.

The below example illustrates this unusual behavior of NULL:

Sample script:

/* Unusual behavior of NULL in NOT IN */

create table lci_work_tbls.tera11

(

col1 integer

) ;

insert into lci_work_tbls.tera11 values (1);

insert into lci_work_tbls.tera11 values (2);

insert into lci_work_tbls.tera11 values (3);

create table lci_work_tbls.tera12

(

col2 integer

) ;

insert into lci_work_tbls.tera12 values (1);

insert into lci_work_tbls.tera12 values (null);

insert into lci_work_tbls.tera11 values (3);

/* Test to find the rows returned using NOT IN */

select col1 from lci_work_tbls.tera11

where col1 not in (select col2 from lci_work_tbls.tera12)

;

Result - NO ROWS RETURNED

Like us on Facebook or Google+ if you like our posts. Share what topics you want us to cover.

Please donate if you are helped. Your donation will help those who need help !!