compare two tables
-
hi
ive got 2 tables that have the same columns but one table has more rows than the other
how can i compare between the two tables and find out
which rows are missing in the second table ????
or even better - compare the two tables and get the rows that are only in one table ( but not in both )
Example:
table X table Y
column A column B column A column B
1 ss 1 ss
2 aa 2 aa
2 ee 5 aa
4 aa 4 ee
5 aa
solution should be:
2 ee
4 aa
4 ee
-
SELECT 'Y' as MissingFromTableName
, x.A, x.b
from X
where not exists
(select 'any old constant'
from Y
where Y.a = X.a
and Y.b = X.b
)
UNION ALL
SELECT 'X' as MissingFromTableName
, Y.A, Y.b
from Y
where not exists
(select 'any old constant'
from X
where Y.a = X.a
and Y.b = X.b
)
SQL = Scarcely Qualifies as a Language
-
thats great
thx
-
You can make it even simpler by just using a FULL OUTER JOIN, example:
CREATE TABLE TEST1 (COL1 VARCHAR(10), COL2 INT)
CREATE TABLE TEST2 (COL1 VARCHAR(10), COL2 INT)
INSERT INTO TEST1 VALUES ('A', 1)
INSERT INTO TEST1 VALUES ('B', 2)
INSERT INTO TEST2 VALUES ('A', 1)
INSERT INTO TEST2 VALUES ('A', 2)
INSERT INTO TEST2 VALUES ('C', 3)
INSERT INTO TEST2 VALUES ('Z', 4)
GO
SELECT
TEST1.COL1 TEST1_COL1,
TEST1.COL2 TEST1_COL2,
TEST2.COL1 TEST2_COL1,
TEST2.COL2 TEST2_COL2
FROM dbo.TEST1
FULL OUTER JOIN dbo.TEST2
ON TEST1.COL1 = TEST2.COL1
AND TEST1.COL2 = TEST2.COL2
WHERE TEST1.COL1 IS NULL OR TEST2.COL1 IS NULL
--Output
TEST1_COL1 TEST1_COL2 TEST2_COL1 TEST2_COL2
---------- ----------- ---------- -----------
NULL NULL A 2
NULL NULL C 3
NULL NULL Z 4
B 2 NULL NULL
-
thx - thats even better 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply