Joining null in SQL Server, Oracle and informatica

Question

Hi!

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???

Coz, 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.

Thanks

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 I can think of are

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.

Answer 2

Perhaps you want a left outer join? See wikipedia

Here's how you do it with Oracle

Here's the SQL Server documentation for left outer join.