Joining null in SQL Server, Oracle and informatica
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
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
.
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
- 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)
- Use both an IS NULL and equality check on all joining columns.
- Use INTERSECT (nulls will be treated as equal). Possibly in a derived table that you can JOIN back onto.
精彩评论