When an input to the Lookup condition is NULL, a data object Lookup transformation returns a single row with null values for output-only ports, and values from input rows for pass-through ports.

For example, the following lookup condition performs a lookup on a data source that contains one or more rows where the value for employee_ID is NULL:

In this example, you use a lookup table with the following data:

EMPLOYEE_ID	LAST_NAME
1294765	Hara
1356356	Carver
1407207	NULL
1570348	Draper
NULL	Limonov

You compare the following input values from your data source with the lookup table:

EMPLOYEE_NUMBER
---------------
1294765
1356356
1407207
1648246
NULL

In this example, the Lookup condition produces the following results:

1294765,Hara
1356356,Carver
1407207,NULL
NULL,NULL
NULL,NULL

The Lookup condition finds matches between EMPLOYEE_ID and EMPLOYEE_NUMBER for the first two rows. For the third row, the lookup source contains a row with a NULL value that does not participate in the lookup condition. It matches the lookup condition, and returns a result with the NULL value for the non-lookup column.

For the fourth and fifth rows, the Lookup condition does not find a match, and returns NULL for both values. For the fifth row, note that the Lookup condition does not find a match again, because NULL does not match anything, including NULL.