Query to find delta / exceptions between 2 tables

Hello,

try to use EXCEPT command..

SELECT *
FROM Table A
EXCEPT
SELECT *
FROM TableB

Select allOpen in new window

SELECT serial_number
FROM tableA
EXCEPT
SELECT serial_number
FROM tableB

great, so is that basically a translation of

select * from TableA where SERIAL_NUMBER does not equal (select serial_number from TableB);

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.

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);

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.

Vitor,

What does the SELECT 1 represent in the sub SELECT query?

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.

thanks for clarifying.  I will keep that trick in mind going forward.

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

Select allOpen in new window

Currently in Access, but I will move to SQL if the report i am building seems beneficial.

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".

worked perfectly and at an optimal speed.