locked
how to handle nulls in join conditions RRS feed

  • Question

  • hi,

    Select 6 A,8 B INTO #TEmp1
    SELECT 1 A,8 B INTO #temp2

    Select * from #Temp1
    Select * from #temp2

    Select (ISNULL(t1.A,0) - ISNULL(t2.A,0))
    from #Temp1 t1 join #temp2 t2 On t1.B = t2.B
    RESULT : 5
    Delete from #Temp1

    Select (ISNULL(t1.A,0) - ISNULL(t2.A,0))
    from #Temp1 t1 join #temp2 t2 On t1.B = t2.B
    RESULT : NULL

    WHAT SHOULD I DO TO GET result 5 in next 2nd query too

    Thanks.

    Wednesday, February 8, 2012 6:52 AM

Answers

  • thanks for reply.

    we should use FULL JOIN .

    • Marked as answer by Sudeep Puvvadi Wednesday, February 8, 2012 7:01 AM
    Wednesday, February 8, 2012 6:59 AM

All replies

  • Use RIGHT JOIN in the second query and get -1,  there is no data at all in tem1 how would want to get 5?

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Wednesday, February 8, 2012 6:56 AM
    Answerer
  • thanks for reply.

    we should use FULL JOIN .

    • Marked as answer by Sudeep Puvvadi Wednesday, February 8, 2012 7:01 AM
    Wednesday, February 8, 2012 6:59 AM
  • No , FULL JOIN still return -1, have you ran  your script?

    Select (ISNULL(t1.A,0) - ISNULL(t2.A,0))
    from #Temp1 t1 FULL join #temp2 t2 On t1.B = t2.B


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Wednesday, February 8, 2012 7:18 AM
    Answerer