Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Comparing Two Different Tables, Showing Values In Table 1 Not in Table 2 Expand / Collapse
Author
Message
Posted Monday, April 15, 2013 7:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 13, 2013 8:55 PM
Points: 18, Visits: 68
Hello,

I'd like to compare two tables, and show values found in table 1 which are NOT in table 2, then same for table 2, show values in table 2 which are NOT in table 1.

Here's what I came up with, looks ok?

select T1.[Name], T2.[Address]
from [dbo].[My Table 1] as T1
left join [dbo].[My Table 2] as T2 on T1.[Name]=T2.[Name]
where T2.[Address] is not null

Thanks
Post #1442555
Posted Tuesday, April 16, 2013 1:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:09 AM
Points: 6,750, Visits: 13,896
SELECT
TableSource = CASE WHEN T1.[Name] IS NULL THEN 'Table2' ELSE 'Table1' END,
T1.[Name],
T1.[Address],
T2.[Name],
T2.[Address]
FROM [dbo].[My Table 1] T1
FULL OUTER JOIN [dbo].[My Table 2] T2
ON T1.[Name] = T2.[Name]
WHERE NOT T1.[Name] = T2.[Name]



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1442619
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse