CODEWITHSUNDEEP
sqlsql-server
Navyseal
Navyseal

Reputation: 901

Comparing data between two tables without except operator when having null values in fields

I have this query in SQL Server which will give me the difference between two tables

SELECT * FROM dbo.emp
except
SELECT * FROM #temp1

and I get the proper result with only one record (which is correct)

But when I use a left outer join

SELECT emp.* FROM emp
LEFT JOIN #temp1 ON emp.empid = #temp1.empid 
and
emp.firstname = #temp1.firstname 
AND emp.lastname = #temp1.lastname 
and emp.salary = #temp1.salary
and emp.dob = #temp1.dob
WHERE #temp1.empid IS NULL;

I get 39 records. Why the difference? I have mentioned all the columns in my join condition.

I know how to do this via where clause but my intention is to learn why the outer join is not working. Basically what is it that I am doing wrong

The below code works

SELECT dbo.emp.* FROM dbo.emp
JOIN #temp1 ON emp.empid = #temp1.empid 
where
emp.firstname <> #temp1.firstname 
or emp.lastname <> #temp1.lastname 
or emp.salary <> #temp1.salary
or emp.dob <> #temp1.dob;

Upvotes: 2

Views: 1494

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270411

The outer join is presumably not working because some of the fields have NULL values.

You can emulate the except using union all and group by:

SELECT emp.*
FROM ((select 'emp' as which, empid, firstname, lastname, salary, dob
       from emp
      ) union all
      (select 'temp', empid, firstname, lastname, salary, dob
       from @temp1
      )
     ) t
group by empid, firstname, lastname, salary, dob
having sum(case when which = 'temp' then 1 else 0 end) = 0;

EDIT:

You can do this with a join using more complex conditions:

SELECT emp.*
FROM emp LEFT JOIN
     #temp1
     ON (emp.empid = #temp1.empid or coalesce(emp.empid, #temp1.empid) is null) and
        (emp.firstname = #temp1.firstname or coalesce(emp.firstname, #temp1.firstname) is null) and
        (emp.lastname = #temp1.lastname or coalesce(emp.lastname, #temp1.lastname) is null)  and
        (emp.salary = #temp1.salary or coalesce(emp.salary,  #temp1.salary) is null) and
        (emp.dob = #temp1.dob or  or coalesce(emp.dob, #temp1.dob ) is null)
WHERE #temp1.empid IS NULL;

Upvotes: 3

Related Questions