• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 197
  • Last Modified:

Query to find delta / exceptions between 2 tables

I have two tables (tableA, tableB) which basically contain the same data.  The key between the 2 is serial_number.  I need a query to return the delta between the 2 tables.

TableA contains all the data that should be in TableB but that is not the case.  I need to determine which serial numbers from TableA are not appearing in TableB.
0
szadroga
Asked:
szadroga
  • 6
  • 2
  • 2
  • +3
1 Solution
 
Haris DjulicCommented:
Hello,

try to use EXCEPT command..
SELECT *
FROM Table A
EXCEPT
SELECT *
FROM TableB

Open in new window

0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
SELECT serial_number
FROM tableA
EXCEPT
SELECT serial_number
FROM tableB
0
 
szadrogaAuthor Commented:
great, so is that basically a translation of

select * from TableA where SERIAL_NUMBER does not equal (select serial_number from TableB);
0
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'.

 
szadrogaAuthor Commented:
I want to return more fields in the query, not just serial_number but serial_number is the field I need to base the "delta" logic on.
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
I don't think either of ours are that exactly.

Haris compares the entirety of all of the fields, and only where all the fields are identical in both tables for a particular row, then exclude that.

Mine shows only the serial_number, and excludes tableB's serial numbers; it doesn't show all the fields.

If you want to show everything (as you have said "Select *"), then

select * from TableA where SERIAL_NUMBER not in (select serial_number from TableB);
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I prefer the NOT EXISTS clause:
SELECT *
FROM TableA
WHERE NOT EXISTS (SELECT 1 
              FROM TableB
              WHERE TableB.serial_number=TableA.serial_number)

Open in new window

0
 
PatHartmanCommented:
Or, you can use a left join.
Select *
From tblA Left Join tblB On tblA.SerialNumber = tblB.SerialNumber
Where tblB.SerialNumber is Null;

"EXCEPT" is not valid Access SQL Syntax.
0
 
szadrogaAuthor Commented:
Vitor,

What does the SELECT 1 represent in the sub SELECT query?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
SELECT clause needs at least a column or value. 1 is a constant value so it's used to avoid access to data. It's an old trick ;)
Could be SELECT NULL or SELECT 'A' or any constant value. It's better than a SELECT * since you won't need the sub-query to return any data.
0
 
szadrogaAuthor Commented:
thanks for clarifying.  I will keep that trick in mind going forward.
0
 
aikimarkCommented:
Is the data in an Access database or a SQL Server database?  If an Access, the EXCEPT keyword is not supported. (confirmed Pat's comment)

You can do this in a single UNION query.
Example:
Select tblA.*, "In A Not In B" As tblDiff
From tblA Left Join tblB On tblA.SerialNumber = tblB.SerialNumber
Where tblB.SerialNumber is Null
UNION ALL
Select tblB.*, "In B Not In A" As tblDiff
From tblB Left Join tblA On tblA.SerialNumber = tblB.SerialNumber
Where tblA.SerialNumber is Null
UNION ALL
Select tblA.*, "Field difference" As tblDiff
From tblA Join tblB On tblA.SerialNumber = tblB.SerialNumber
Where tblA.Field1 <> tblB.Field1
OR tblA.Field2 <> tblB.Field2
OR tblA.Field3 <> tblB.Field3
OR tblA.Field4 <> tblB.Field4

Open in new window

0
 
szadrogaAuthor Commented:
Currently in Access, but I will move to SQL if the report i am building seems beneficial.
0
 
PatHartmanCommented:
The suggestion I made of the outer join and the suggestion of Not Exists will work in both variants of SQL.  But if you are going to use T-SQL (with a pass-through query) you also have the option of "EXCEPT".
0
 
szadrogaAuthor Commented:
worked perfectly and at an optimal speed.
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

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 6
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now