Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 527
  • Last Modified:

Query 2 tables to view UNCOMMON records.

I am trying to create a query that compares 2 tables and returns the records that are not common to both.  I have tried every combination of INNER, OUTER joins, but can't seem to get it.

For example:

INITIALLY:
Table A records:   record1, record2, record3, record4, record5
Table B records: none.

I insert into Table B:  record 1, record2

I now want the query for Table A to showonly : record3, record4, record5
. . . excluding the 2 records that now exist in Table B

Tnaks in advance.

Maxwell
0
MaxwellTurner
Asked:
MaxwellTurner
  • 5
  • 4
  • 2
  • +1
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Use the 'Find Unmatched Query Wizard by selecting Queries on the left, hit the 'New' button, and choosing 'Find Unmatched Query Wizard' from the dialog box.  Then, follow the graphical UI to select your tables, how they are joined, and what to display.

Hope this helps.
-Jim
0
 
NestorioCommented:
Select * from table_a where id not in (select id from table_b)
Union
Select * from table_b where id not in (select id from table_a)
0
 
shanesuebsahakarnCommented:
Don't use IN if you can help it, as the performance is very poor. It is much more efficient to use joins:

SELECT * FROM TableA LEFT JOIN TableB ON TableA.IDField=TableB.IDField WHERE TableB.IDField IS NULL

This will show all records in TableA which do not exist in Table B. It presumes a primary key called IDField.
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
NestorioCommented:
I would like to do a performance test.

Anyway the solution with a join query would be:

SELECT * FROM TableA LEFT JOIN TableB ON TableA.IDField=TableB.IDField WHERE TableB.IDField IS NULL
UNION
SELECT * FROM TableB LEFT JOIN TableA ON TableA.IDField=TableB.IDField WHERE TableA.IDField IS NULL
0
 
shanesuebsahakarnCommented:
Nestorio, please do perform your performance test. You'll find that what I said is correct - it is a well known limitation of the IN clause. If you must use a subquery, EXISTS is also a viable alternative.
0
 
MaxwellTurnerAuthor Commented:
I tried JH's advice before I got the other comments & it worked exactly how I wanted it to ( I feel kinda dumb, having not noticed that Query Wizard before!!!).  I however, ended up with this query:

SELECT FROM TableA LEFT JOIN TableB ON 'TableA.IDfield'= 'TableB.IDfield'
WHERE (((TableA.Field)=#passed variable#) AND ((TableA.IDfield) Is Null))

. . . I didn't seem to need the UNION and the other SELECT?

I am using this on a webpage built using Coldfusion - 2 frames (left & right) - Basically I am passing the records back & forth between the Left & Right frames & refreshing them each time.  The Left frames displays all the records initially, but when I pass a record to the Right frame, it is displayed there and as the Left frame refreshes, it disappears from that list.

I also pass a variable which filters the query.

Anyways, thanks for all the help.  I am interested in the difference between my query and the suggestions posted by Nestorio.  BTW I am not using IN.

Thanks,
Maxwell
0
 
NestorioCommented:
Maxwell,

<<I am trying to create a query that compares 2 tables and returns the records that are not common to both>>

Try with these data sets:

Table A
1
2
3
4

Table B
3
4
5
6

Result (not common to both tables)

1
2
5
6
0
 
MaxwellTurnerAuthor Commented:
That is exactly what I am trying to accomplish, but it seems to work without the UNION and extra SELECT (from your post) as I mentioned earlier.  I guess there are 2 ways of doing this.  I get the same results using only:

SELECT * FROM TableA LEFT JOIN TableB ON TableA.IDField=TableB.IDField WHERE TableB.IDField IS NULL


Maxwell
0
 
NestorioCommented:
I think your query would return (based on my example):

Result
1
2

But not
1
2
5
6

What is wanted.
Or.. maybe I'm wrong...
0
 
shanesuebsahakarnCommented:
Nestorio's query returns records in A but not in B, AND records in B but not in A.
0
 
NestorioCommented:
Shane,

I did the performance test with:

TableA (2 fields, 100,000 records)
TableB (2 fields, 100,000 records)

I ran 2 queries to get records in TableA that didn't match in TableB.
One query with the clause NOT IN and the other With a Left join clause.
There were 50,000 records that didn't match.

Both queries lasted almost the same, about 25 seconds (with my environment of software and hardware).
0
 
shanesuebsahakarnCommented:
Try it with more complex queries that perform grouping. The other place where you notice severe performance hits are text fields. Also, up to a point depends how you've written your query.

I'll dig out some articles for you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now