Joining null in SQL Server, Oracle and informatica?

Question

I have two tables to join with a column (say emp_id).. if emp_id in both the tables have null values, how will SQL Server and Oracle treat???

I read that informatica will neglect the NULL rows when joining..if I handle the null, by substituting -1, a cross-join will happen which i don't want..

What can I do here? (I cannot completely neglect the rows which has NULL)

Answer 1

You can't join on colA = colB and expect NULLs to compare as equal. Depending on your needs (assuming perhaps some sort of table synchronisation need below) three approaches can be done :

1. Use COALESCE to substitute a value such as -1 in place of null if a suitable value exists that can never occur in your actual data. COALESCE(Table1.colA,-1) = COALESCE(Table2.colB,-1)

2. Use both an IS NULL and equality check on all joining columns.

3. Use INTERSECT (nulls will be treated as equal). Possibly in a derived table that you can JOIN back onto.