0

image_table

I currently need to find all the differences between a new_master dataset and a previous one using SQL Oracle. The datasets have the same structure and consist of both integers and strings and do not have a unique key id unless I select several columns together. You can see an image at the beginning as image_table. I found online this code and I wanted to ask you if you have any advices.

SELECT n.*
FROM new_master as n
LEFT JOIN old_master as o
ON (n.postcode = o.postcode)
WHERE o.postcode IS NULL
SORT BY postcode

In doing so I should get back all the entries from the new_master that are not in the old one.

Thanks

forpas
  • 160,666
  • 10
  • 38
  • 76
Luigi
  • 59
  • 1
  • 6
  • `SORT BY` not exists, use `ORDER BY`. – Akina Sep 24 '20 at 12:54
  • *find all the differences between a new_master dataset and a previous one* Does these two datasets means 2 different tables each with a dataset, or you mean a subsets from the same table? And specify your MySQL version - it is important. – Akina Sep 24 '20 at 12:56
  • Please provide sample data and desired results. – Gordon Linoff Sep 24 '20 at 13:00
  • MYSQL or ORACLE or both? – P.Salmon Sep 24 '20 at 13:09
  • Hi all, I added an image of the table at the beginning as image_table, the datasets are in structure the same but 2 different years and I need to check the differences. It' Oracle. Thanks – Luigi Sep 24 '20 at 13:19

1 Answers1

0

If you are in an Oracle databse, there are a couple queries that can help you find any differences.

Find any records in OLD that are not in NEW.

SELECT * FROM old_master
MINUS
SELECT * FROM new_master;

Find any records in NEW that are not in OLD.

SELECT * FROM new_master
MINUS
SELECT * FROM old_master;

Count number of items in OLD

SELECT COUNT (*) FROM old_master;

Count number of items in NEW

SELECT COUNT (*) FROM new_master;

The COUNT queries are needed in addition to the MINUS queries in case there are duplicate rows with the same column data.

EJ Egyed
  • 5,791
  • 1
  • 8
  • 23