question

sqlLearner 1 avatar image
sqlLearner 1 asked

Compare two queries

I have two queries that I am trying to compare to find which records exist in one and not the other. My first query retruns 523 rows SELECT * FROM MasterList a1 LEFT JOIN tblInfo a2 ON a1.FIRSTNAME=a2.FirstName AND a1.LASTNAME=a2.LastName AND a1.DOB=a2.Birthdate WHERE a2.ID IS null My second query returns 105 Rows SELECT * FROM MasterList a1 LEFT JOIN tblInfo a2 ON a1.ID=a2. WHERE a2.ID IS null Now I have done a UNION to get a distinct list of records from both. But what I also want is a query that shows me What records in query 1 or also in query 2 and vice versa...Any ideas? I tried an exists clause t but not sure if I did it correctly? Below was my attempt it is teurning 523 rows still SELECT * FROM MasterList a1 left JOIN tblInfo a2 ON a1.FIRSTNAME=a2.FirstName AND a1.LASTNAME=a2.LastName AND a1.DOB=a2.Birthdate WHERE a2.ID IS NULL AND EXISTS ( SELECT * FROM MasterList a1 left JOIN tblInfo a2 ON a1.ID= a2.ID WHERE a2.ID IS NULL )
tsqlunionexists
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image
KenJ answered
use [EXCEPT][1] without copying and pasting your entire query, this gives you everything from the first query that's different or new from the second query: select * from query1 EXCEPT select * from query2 and this gives you everything in the second query that's different or new in the first query select * from query2 EXCEPT select * from query1 [1]: http://msdn.microsoft.com/en-us/library/ms188055.aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sacred Jewel avatar image
Sacred Jewel answered
> But what I also want is a query that > shows me What records in query 1 or > also in query 2 and vice versa...Any > ideas? I tried an exists clause t but > not sure if I did it correctly I am not sure either what you are after ;) @Ken Johnson thinks it is the case for EXCEPT and I got the feeling it is the case for INTERSECT ;) Only you know which one suits your situation select * from query1 INTERSECT select * from query2 and vice versa ...;)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Might be a case for both :)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.