<p>I recently had to solve this problem and find I've needed this info many times in the past so I thought I would post it.  Assuming the following table def, how would you write a query to find all differences between the two?</p>

<p>table def:</p>

<pre><code>CREATE TABLE feed_tbl
(
code varchar(15),
name varchar(40),
status char(1),
update char(1)
CONSTRAINT feed_tbl_PK PRIMARY KEY (code)

CREATE TABLE data_tbl
(
code varchar(15),
name varchar(40),
status char(1),
update char(1)
CONSTRAINT data_tbl_PK PRIMARY KEY (code)
</code></pre>

<p>Here is my solution, as a view using three queries joined by unions.  The <code>diff_type</code> specified is how the record needs updated: deleted from <code>_data(2)</code>, updated in <code>_data(1)</code>, or added to <code>_data(0)</code></p>

<pre><code>CREATE VIEW delta_vw AS (
SELECT     feed_tbl.code, feed_tbl.name, feed_tbl.status, feed_tbl.update, 0 as diff_type
FROM         feed_tbl LEFT OUTER JOIN
                      data_tbl ON feed_tbl.code = data_tbl.code
WHERE     (data_tbl.code IS NULL)

UNION

SELECT     feed_tbl.code, feed_tbl.name, feed_tbl.status, feed_tbl.update, 1 as diff_type
FROM         data_tbl  RIGHT OUTER JOIN
                      feed_tbl ON data_tbl.code = feed_tbl.code
where (feed_tbl.name &lt;&gt; data_tbl.name) OR
(data_tbl.status &lt;&gt; feed_tbl.status) OR
(data_tbl.update &lt;&gt; feed_tbl.update) 


UNION

SELECT     data_tbl.code, data_tbl.name, data_tbl.status, data_tbl.update, 2 as diff_type
FROM         feed_tbl LEFT OUTER JOIN
                      data_tbl ON data_tbl.code = feed_tbl.code
WHERE     (feed_tbl.code IS NULL)

)
</code></pre>

[{'answer_id': 31043, 'author': 'wcm', 'author_id': 2173, 'author_profile': 'https://Stackoverflow.com/users/2173', 'pm_score': 0, 'selected': False, 'text': "<p>I would use a minor variation in the second <code>union</code>:</p>\n\n<pre><code>where (ISNULL(feed_tbl.name, 'NONAME') &lt;&gt; ISNULL(data_tbl.name, 'NONAME')) OR\n(ISNULL(data_tbl.status, 'NOSTATUS') &lt;&gt; ISNULL(feed_tbl.status, 'NOSTATUS')) OR\n(ISNULL(data_tbl.update, '12/31/2039') &lt;&gt; ISNULL(feed_tbl.update, '12/31/2039')) \n</code></pre>\n\n<p>For reasons I have never understood, <code>NULL</code> does not equal <code>NULL</code> (at least in SQL Server).</p>\n"}, {'answer_id': 31060, 'author': 'hova', 'author_id': 2170, 'author_profile': 'https://Stackoverflow.com/users/2170', 'pm_score': 0, 'selected': False, 'text': '<p>You could also use a <code>FULL OUTER JOIN</code> and a <code>CASE ... END</code> statement on the <code>diff_type</code> column along with the aforementioned <code>where</code> clause in <a href="https://stackoverflow.com/questions/30985/querying-2-tables-with-the-same-spec-for-the-differences#31043">querying 2 tables with the same spec for the differences</a>   </p>\n\n<p>That would probably achieve the same results, but in one query.</p>\n'}, {'answer_id': 31061, 'author': 'JosephStyons', 'author_id': 672, 'author_profile': 'https://Stackoverflow.com/users/672', 'pm_score': 2, 'selected': False, 'text': '<p>UNION will remove duplicates, so just UNION the two together, then search for anything with more than one entry.  Given "code" as a primary key, you can say:</p>\n\n<p><em>edit 0: modified to include differences in the PK field itself</em></p>\n\n<p><em>edit 1: if you use this in real life, be sure to list the actual column names.  Dont use dot-star, since the UNION operation requires result sets to have exactly matching columns.  This example would break if you added / removed a column from one of the tables.</em></p>\n\n<pre><code>select dt.*\nfrom\n  data_tbl dt\n ,( \n  select code\n  from\n    (        \n    select * from feed_tbl\n    union\n    select * from data_tbl        \n    )\n  group by code\n  having count(*) &gt; 1    \n  ) diffs  --"diffs" will return all differences *except* those in the primary key itself \nwhere diffs.code = dt.code\nunion  --plus the ones that are only in feed, but not in data\nselect * from feed_tbl ft where not exists(select code from data_tbl dt where dt.code = ft.code)\nunion  --plus the ones that are only in data, but not in feed\nselect * from data_tbl dt where not exists(select code from feed_tbl ft where ft.code = dt.code)\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/30985', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/292/']