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.
szadrogaAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Vitor MontalvãoConnect With a Mentor MSSQL 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
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
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
 
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
All Courses

From novice to tech pro — start learning today.