How to quickly compare two datasets using a generic & powerful SQL query
A step-by-step guide to ease datasets comparison via a ready-to-use Structured Query Language template
Artefact Engineering and Data Science
Introduction
In their daily activities, Data Engineers and Data Analysts are required to improve data ingestion processes. Beyond the usual unit tests, it may be interesting to easily and quickly compare two datasets (i.e. tables, views, queries, etc) for different purposes such as impact analysis or non-regression testing. Also, discrepancies identification between two points of view of a snapshot table is quite useful for adhoc analysis or debugging.
With this in mind and for SQL practitioners, a pre-built and reusable script makes sense, hence the purpose of this article.
Ready-to-use SQL template explained
Let’s go to the heart of the matter with the expected query :
Now, let’s explain it :
- First, we use CTE — Common Table Expressions ( WITH . AS statements) to identify the two datasets to be compared. Thereby, the following query lines can stay unchanged.
- Then, we construct two similar queries aliased dataset_1_only and dataset_2_only in order to keep rows that are only present in dataset 1 (compared with dataset 2) and vice-versa thanks to the EXCEPT set operator.
- Finally, a flag complementary field is defined in order to identify the source of mismatching rows from both queries. These are then gathered via the UNION ALL set operator.
Note: obviously, having two datasets with comparable fields is a prerequisite to use the above query
Illustrated example
Enough with theory, now to practice! Suppose a supermarket wanting to retrieve all products eligible for a promotion in a single table according to the week promotion conditions.
As an initial statement, we consider below products in stock:
According to the promotion conditions in week 1 :
- 10% discount on fruits, vegetables and herbs shelves
the dataset_1 is:
In week 2, the promotion conditions evolve to:
- 10% discount on fruits and ice-creams shelves
- 20% discount on vegetables shelf
- no discount on herbs shelf
The data pipeline evolution generates the dataset_2 :
But… are we really confident with the results? Typical questions are:
- is there any missing or extra-rows?
- are implemented evolutions correct on impacted fields?
- is there any regression on non-impacted fields?
Note: even if it seems easy to answer above questions intuitively due to a trivial example on a small dataset, in real use cases we usually face plenty of rows and columns from complex queries (transformations, joins, aggregates, windows functions, …) letting this query take on its full meaning
Let’s try to answer the 3 questions interpreting the comparison query results:
- No row with products from the fruits shelf: fine as the “10% discount on fruits” is remained unchanged from week 1 to week 2
- Product_ID #50 (herbs shelf) is missing from dataset_2 (only in dataset_1 ): fine as the “discount on herbs” was stopped on week 2
- Product_ID #60 (ice-creams shelf) is missing from dataset_1 (only in dataset_2 ): fine as the “discount on ice-creams” was introduced on week 2
- Rows concerning Product_ID #30 and #40 (vegetables shelf) are diverging on reduced_price field: fine as the “discount on vegetables” increased from 10% on week 1 to 20% on week 2
Finally, everything looks good:
- is there any missing or extra-rows? No!
- are implemented evolutions correct on impacted fields? Yes!
- is there any regression on non-impacted fields? No!
Tips and tricks
In CTE, even if you can copy-paste your ingestion SQL query before/after evolution, it could be a good idea to store the results in temporary tables to simplify the comparison, improve the query performances and benefit from caching if you run it multiple times.
In real-world, divergences between two datasets can be messy. Then, sorting results by key (the granularity of the datasets) and flag can hugely help interprating and comparing equivalent rows coming from both datasets.
To ease the investigations and identify the gap origin, the suspicious datasets common fields can be removed (i.e. commented in CTE) from comparison: if there is no result, it means all compared fields are equal. You can then focus on remaining suspicious fields only for the next comparison and make it step-by-step.
Note: an equivalent analysis could have been performed using LEFT JOIN strategy but would have been much more difficult to maintain (NULLs & fields comparison management) and less efficient as set operators are more powerful than joins.
Summary
This templated query therefore makes it easier for developers to quickly validate changes on complex data pipelines. It is useful as a complement to more traditional unit tests, and can even be considered in a more general way when comparing any two datasets with similar structures. Last but not least, the logic of this query is even easy to learn by heart!
Thank you for reading, I hope it was clear and I would be glad to hear your feedback 🙂
sql query to return differences between two tables
I am trying to compare two tables, SQL Server, to verify some data. I want to return all the rows from both tables where data is either in one or the other. In essence, I want to show all the discrepancies. I need to check three pieces of data in doing so, FirstName, LastName and Product.
I’m fairly new to SQL and it seems like a lot of the solutions I’m finding are over complicating things. I don’t have to worry about NULLs.
I started by trying something like this:
I’m having trouble taking this further though.
Based on the answer by @treaschf I have been trying to use a variation of the following query:
But I keep getting 0 results back, when I know that there is at least 1 row in td that is not in d.
Ok, I think I figured it out. At least in my few minutes of testing it seems to work good enough.
This is basically going to tell me what is in my test data that is not in my real data. Which is completely fine for what I need to do.
15 Answers 15
IF you have tables A and B , both with colum C , here are the records, which are present in table A but not in B :
To get all the differences with a single query, a full join must be used, like this:
What you need to know in this case is, that when a record can be found in A , but not in B , than the columns which come from B will be NULL, and similarly for those, which are present in B and not in A , the columns from A will be null.
I know that this may not be a popular answer but I do agree with @Randy Minder on using third party tool when more complex comparison is needed.
This specific case here is easy and for this case such tools are not needed but this can get complex easily if you introduce more columns, databases on two servers, more complex comparison criteria and such.
There are a lot of these tools such as ApexSQL Data Diff or Quest Toad and you can always use them in trial mode to get the job done.
To get all the differences between two tables, you can use like me this SQL request :
Simple variation on @erikkallen answer that shows which table the row is present in:
If you get an error
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
then it may help to add
If you want to get which column values are different, you could use Entity-Attribute-Value model:
Presenting the Cadillac of Diffs as an SP. See within for the basic template that was based on answer by @erikkallen. It supports
- Duplicate row sensing (most other answers here do not)
- Sort results by argument
- Limit to specific columns
- Ignore columns (e.g. ModifiedUtc)
- Cross database tables names
- Temp tables (use as workaround to diff views)
Usage:
You can use except , for example something like this :
Much simpler to read.
This will do the trick, similar with Tiago’s solution, return «source» table as well.
result will contain differences between tables, in column _tabloc you will have table reference.
For a simple smoke test where you you’re trying to ensure two tables match w/out worrying about column names:
You can easily write a store procedure to compare a batch of tables.
There is a performance issue related with the left join as well as full join with large data.
In my opinion this is the best solution:
EXCEPT and NOT EXISTS are good ways to quickly find differences between sets of data, but I often want to know exactly which columns are different and how they are different.
What I find useful in these situations is using UNPIVOT to convert columns into key value pairs that one can compare row for row.
In order to be useful though, there needs to be a way to match the records you want to compare, in your example something like a social security number or a "person id":
And the UNPIVOT with a couple of CTEs thrown in:
The result — the 2 products are different:
This is great if you have many, many columns to compare over a large number of rows.
It can take a while to setup, though, and you do need to convert every column value to the same type, which can take a while (notice the CAST for PersonId).
If the dataset is really large you may also want to use temp tables instead of CTEs.
Сравнить две таблицы в MySQL
При переносе данных нам часто приходится сравнивать две таблицы, чтобы определить запись в одной таблице, у которой нет соответствующей записи в другой таблице.
Например, у нас есть новая база данных, схема которой отличается от устаревшей базы данных. Наша задача — перенести все данные из устаревшей базы данных в новую и убедиться, что данные были перенесены правильно.
Чтобы проверить данные, нам нужно сравнить две таблицы, одну в новой базе данных и одну в устаревшей базе данных, и идентифицировать несопоставленные записи.
Предположим, у нас есть две таблицы: my_table и you_table. Следующие шаги сравнивают две таблицы и идентифицируют несопоставленные записи:
Во-первых, используйте оператор UNION для объединения строк в обеих таблицах; включать только столбцы, которые нужно сравнить. Возвращенный набор результатов используется для сравнения.
Во-вторых, сгруппируйте записи на основе первичного ключа и столбцов, которые необходимо сравнить. Если значения в столбцах, которые необходимо сравнить, идентичны, COUNT(*) возвращается 2, в противном случае COUNT(*) возвращается 1.
Смотрите следующий запрос:
Если значения в столбцах, участвующих в сравнении, идентичны, строка не возвращается.
Пример сравнения двух таблиц в MySQL
Давайте посмотрим на пример, который имитирует шаги выше.
Сначала создайте 2 таблицы с похожей структурой:
Во-вторых, вставьте некоторые данные в таблицы my_table и you_table:
В-третьих, сравните значения id и столбца title обеих таблиц:
Возвращенных строк не будет, потому что нет несоответствующих записей.
В-четвертых, вставьте новую строку в таблицу you_table:
В-пятых, выполните запрос, чтобы снова сравнить значения столбца заголовка в обеих таблицах. Новая строка, которая является несопоставленной строкой, должна вернуться.
В этой статье вы узнали, как сравнивать две таблицы на основе определенных столбцов, чтобы найти несопоставленные записи.
Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.
SQL сравнивает данные из двух таблиц
Как написать SQL, чтобы проверить, что если TableA и TableB , имеющие одинаковые первичные ключи, содержат одинаковое значение в каждом столбце.
Это означает, что эти две таблицы имеют точно такие же данные.
ОТВЕТЫ
Ответ 1
Вы должны иметь возможность «MINUS» или «EXCEPT» в зависимости от вкуса SQL, используемого вашей СУБД.
Если запрос не возвращает строк, то данные будут точно такими же.
Ответ 2
Использование реляционных операторов:
Измените EXCEPT на MINUS для Oracle.
Слегка придирчивая точка: вышеупомянутое полагается на приоритет оператора, который, согласно стандарту SQL, зависит от реализации, поэтому YMMV. Он работает для SQL Server, для которого приоритет:
- Выражения в круглых скобках
- INTERSECT
- EXCEPT и UNION оцениваются слева направо.
Ответ 3
dietbuddha имеет приятный ответ. В тех случаях, когда у вас нет MINUS или EXCEPT, один из вариантов состоит в том, чтобы сделать объединение между таблицами, группировать по всем столбцам и убедиться, что есть два из всего:
Ответ 4
Вернет все идентификаторы, которые являются одинаковыми в обеих таблицах. Чтобы получить различия, EXISTS НЕ СУЩЕСТВУЕТ.
Ответ 5
Взяв script из onedaywhen, я изменил его, чтобы также показать, из какой таблицы поступает каждая запись.
Ответ 6
просто для завершения, proc, сохраненный с использованием метода except для сравнения 2 таблиц и получения результата в той же таблице с 3-мя ошибками, ADD, DEL, GAP таблица должна иметь тот же PK, вы объявляете 2 таблицы и поля для сравнения 1 или обеих таблиц
Просто используйте это ps_TableGap ‘tbl1’, ‘Tbl2’, ‘fld1, fld2, fld3’, ‘fld4’fld5’fld6’ (необязательно)
Ответ 7
Усиление ответа на диетубаддха.
Ответ 8
Вы можете найти различия между двумя таблицами, используя комбинацию вставки всех и полного внешнего соединения в Oracle. В sql вы можете извлечь различия с помощью полного внешнего соединения, но кажется, что вставка all/first не существует в sql! Следовательно, вместо этого вы должны использовать следующий запрос:
Хотя использование «ИЛИ» в предложении where не рекомендуется и обычно приводит к снижению производительности, вы все равно можете использовать приведенный выше запрос, если ваши таблицы не являются массивными. Если есть какой-либо результат для вышеупомянутого запроса, это точно различия двух таблиц, основанные на сравнении полей 1,2,3,4. Для повышения производительности запроса вы также можете отфильтровать его по дате (см. Прокомментированную часть).
Ответ 9
Результат равен null, но источники разные!
Ответ 10
У меня была такая же проблема в SQL Server, и я написал этот T-SQL script для автоматизации процесса (на самом деле это версия с увлажненной версией, я написал все данные для отдельной таблицы для удобства отчетности).
Обновите «MyTable» и «MyOtherTable» на имена таблиц, которые вы хотите сравнить.
Ответ 11
Я написал это, чтобы сравнить результаты довольно неприятного представления, которое я портировал с Oracle на SQL Server. Он создает пару временных таблиц, #DataVariances и #SchemaVariances, с различиями в (вы уже догадались) данными в таблицах и схемой самих таблиц.
Он требует, чтобы обе таблицы имели первичный ключ, но вы могли бы поместить его в tempdb с помощью столбца идентификации, если исходные таблицы не имеют его.
Ответ 12
Большинство ответов, кажется, игнорируют проблему, поднятую Камилом. (Вот где таблицы содержат одинаковые строки, но разные таблицы повторяются в каждой таблице.) К сожалению, я не могу использовать его решение, потому что я в Oracle. Лучшее, что я смог придумать, это:
Ответ 13
Мы можем сравнить данные из двух таблиц таблиц DB2, используя следующий простой запрос:
Шаг 1: — Выберите, какие все столбцы нам нужно сравнить из таблицы (T1) схемы (S)
Шаг 2: — Используйте ключевое слово «Минус» для сравнения 2 таблиц.
Шаг 3: — Выберите, какие все столбцы нам нужно сравнить из таблицы (T2) схемы (S)
КОНЕЦ результат: — ВЫБЕРИТЕ T1.col1, T1.col3, T1.col5 из S.T1 МИНУС ВЫБРАТЬ T2.col1, T2.col3, T2.col5 из S.T1;
Если запрос не возвращает строк, данные точно такие же.
Ответ 14
Сравнить T1 (PK, A, B) и T2 (PK, A, B).
Сначала сравните наборы первичных ключей, чтобы найти значения пропущенных ключей с обеих сторон:
Затем перечислите все несоответствия значений:
A и B должны быть одного типа. Вы можете использовать ИНФОРМАЦИОННУЮ СХЕМУ для генерации SELECT. Не забывайте КОАЛЕСС.
Например, для столбцов типа varchar:
Ответ 15
В MySQL, где «минус» не поддерживается и учитывает производительность, это быстрый
Ответ 16
Альтернативный расширенный запрос, основанный на ответе dietbuddha и IanMc. Запрос включает описание, чтобы помочь показать, где строки существуют и отсутствуют. (NB: для SQL Server)
Ответ 17
«Х» — это любое значение.
Переключите таблицы, чтобы увидеть различные расхождения.
Обязательно объедините ключевые поля в ваших таблицах.
Или просто используйте оператор MINUS с двумя операторами выбора, однако, MINUS может работать только в Oracle.