SQL Server: Query to get table incremental updates

Question

I have a table for which I need to know what data has updated from previous day to today. Assuming previous days data is in t1 and current day data in t2, I tried to do "t1 FULL OUTER JOIN t2" on all columns (because I need to get updates on whole table irrespective of any column) where each column of t2 is NULL.

The problem is if a row in t1 has null value in any of its columns and even though the rows remains same in t2, it is resulting in output, which I don't want. Here is an example of my situation.

create table t1(Host varchar(20), location varchar(20), OS varchar(20))
create table t2(Host varchar(20), location varchar(20), OS varchar(20))

  insert into t1 (Host,location,OS)
    values ('Host1','Location1','Linux'),
            ('Host2','Location2','Unix'),
            ('Host3','Location3','Solaris'),
            ('Host4','Location4','Windows'),
            ('Host5',null,'linux') ---> Host5 remains same in both tables

  insert into t2 (Host,location,OS)
    values ('Host1','Location1','Linux'),
            ('Host2','Location2','Unix'),
            ('Host3','Location3','Windows'),
            ('Host4','Location7','Windows'),
            ('Host5',null,'linux') ---> Host5 remains same in both tables

Query:

  SELECT distinct t1.Host, t1.location, t1.OS
  FROM t1 FULL OUTER JOIN
  t2 ON t1.Host = t2.Host 
         AND t1.location = t2.location 
         AND t1.OS = t2.OS
  WHERE (t2.Host IS NULL) OR
        (t2.location IS NULL) OR
        (t2.OS IS NULL)

The output was :

Host    location    OS
---------------------------
NULL    NULL        NULL
Host3   Location3   Solaris
Host4   Location4   Windows
Host5   NULL        linux 

In the expected result Host5 need to be removed as the same row exists in both the table.

I understand this is due to the nature of FULL OUTER JOIN with the WHERE condition and NULL value in data. Just want to know if there is any alternative to get only the updated records.

Answer 1

SELECT distinct t1.Host, t1.location, t1.OS
FROM t1
LEFT JOIN t2 ON 
   t1.Host = 2.Host AND
   COALESCE(t1.location,'<<null>>') = COALESCE(t2.location,'<<null>>') AND 
   t1.OS = t2.OS
WHERE COALESCE(t2.Host,t2.location,t2.OS) is null

Will give you t1 not in t2

SELECT distinct t2.Host, t2.location, t2.OS
FROM t2
LEFT JOIN t1 ON t1.Host = t2.Host AND t1.location = t2.location AND t1.OS = t2.OS
WHERE COALESCE(t1.Host,t1.location,t1.OS) is null

Will give you t2 not in t1

Answer 2

Have a look at EXCEPT and see if it fits your needs

SELECT *
FROM t1
EXCEPT 
SELECT *
FROM t2  

Output:

Host    location    OS 
--------------------------------
Host3   Location3   Solaris 
Host4   Location4   Windows