SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases


Wednesday, April 19, 2006

Select from Two Tables each Column that is Different

SQL Apprentice Question
I have two tables, Table A and Table B. For the sake of this, we will
say that their DDL is identical. They have an associated key, ID.


I need to return for each ID only those columns that are different.


A plain English statement would look like this:
select ID, Column1 from Table A if A.Column1 <> B.Column1.
select ID, Column2 from TableB if A.Column2 <> B.Column2.
. . .


Of course, I want only a single return set with all of the columns that
do not pass the comparison.


What methods of accomplishing this do you recommend?


Celko Answers
CREATE TABLE Alpha
(vague_id INTEGER NOT NULL PRIMARY KEY,
foobar CHAR(5) NOT NULL);

CREATE TABLE Beta
(vague_id INTEGER NOT NULL PRIMARY KEY,
foobar CHAR(5) NOT NULL);


SELECT Alpha.vague_id, Alpha.foobar, Beta.foobar
FROM Alpha
FULL OUTER JOIN
Beta
ON Alpha.vague_id = Beta.vague_id
AND Alpha.foobar <> Beta.foobar;

No comments: