Difference between 2 tables using JOINS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Difference between 2 tables using JOINS

Author

Topic

cheesecake
Starting Member

11 Posts

Posted - 2010-08-13 : 13:26:57

Hi,
I have a table "Test" with 2 columns VehicleId and StandardDeviation and the data looks like

VehicleId Std.Dev.
500 2
500 2
600 3
600 3

I have a copy of this table which has fewer rows but similar data i.e. duplicate values. I am wondering how can I find the difference between these two tables. I want to publish the difference between these two in a diff table i.e.
Rows in table 1 but not in table 2
Rows in table 2 but not in table 1
Rows in both the tables but have different data

Thanks,
CZCaKe

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-13 : 13:48:50

[code]-- records in t2 not in t1
SELECT t1.*
FROM Table1 t1
LEFT JOIN
Table2 t2
On t1.VehicleId = t2.VehicleId
WHERE t2.VehicleId is NULL

-- records in t2 not in t1
SELECT t2.*
FROM Table2 t2
LEFT JOIN
Table1 t1
On t1.VehicleId = t2.VehicleId
WHERE t1.VehicleId is NULL

-- records in both but std dev is different
SELECT *
FROM Table1 t1
JOIN Table2 t2
On t1.VehicleId = t2.VehicleId
WHERE t1.stddev <> t2.stddev

-- All in one shot
SELECT *
FROM Table1 t1
FULL OUTER JOIN
Table2 t2
On t1.VehicleId = t2.VehicleId
WHERE t1.vehicleid is null
OR t2.VehicleId is NULL
OR t1.stddev <> t2.stddev[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources