SQL how to compare two tables for same data content?
How do I write an SQL query to check if TableA and TableB (which have identical primary keys) contain exactly the same values in every column?
It means that these two tables have exactly the same data.
21 Answers 21
You should be able to «MINUS» or «EXCEPT» depending on the flavor of SQL used by your DBMS.
If the query returns no rows then the data is exactly the same.
Using relational operators:
Change EXCEPT to MINUS for Oracle.
Slightly picky point: the above relies on operator precedence, which according to the SQL Standard is implementation dependent, so YMMV. It works for SQL Server, for which the precedence is:
- Expressions in parentheses
- INTERSECT
- EXCEPT and UNION evaluated from left to right.
dietbuddha has a nice answer. In cases where you don’t have a MINUS or EXCEPT, one option is to do a union all between the tables, group by with all the columns and make sure there is two of everything:
Will return all ID’s that are the SAME in both tables. To get the differences change EXISTS to NOT EXISTS.
Taking the script from onedaywhen, I modified it to also show which table each entry comes from.
Enhancement to dietbuddha’s answer.
Clever approach of using NATURAL FULL JOIN to detect the same/different rows between two tables.
Example 1 — status flag:
Example 2 — filtering rows
just to complet, a proc stored using except method to compare 2 tables and give result in same table with 3 errors status, ADD, DEL, GAP table must have same PK, you declare the 2 tables and fields to compare of 1 or both table
Just use like this ps_TableGap ‘tbl1′,’Tbl2′,’fld1,fld2,fld3′,’fld4’fld5’fld6’ (optional)
You can find differences of 2 tables using combination of insert all and full outer join in Oracle. In sql you can extract the differences via full outer join but it seems that insert all/first doesnt exist in sql! Hence, you have to use following query instead:
Although using ‘OR’ in where clause is not recommended and it usually yields in lower performance, you can still use above query if your tables are not massive. If there is any result for the above query, it is exactly the differences of 2 tables based on comparison of fields 1,2,3,4. For improving the query performance, you can filter it by date as well(check the commented part)
Result is null, but sources are different!
I had this same issue in SQL Server and wrote this T-SQL script to automate the process (actually this is the watered-down version, mine wrote all the diff to a single table for easy reporting).
Update ‘MyTable’ and ‘MyOtherTable’ to the names of the tables you wish to compare.
How to compare tables in SQL Server
If you’ve been developing in SQL Server for any length of time, you’ve no doubt hit this scenario: You have an existing, working query that produces results your customers or business owners say are correct. Now, you’re asked to change something, or perhaps you find out your existing code will have to work with new source data or maybe there’s a performance problem and you need to tune the query. Whatever the case, you want to be sure that whatever changes have been made (whether in your code or somewhere else), the changes in the output are as expected. In other words, you need to be sure that anything that was supposed to change, did, and that anything else remains the same. So, how can you easily do that in SQL Server?
In short, I’m going to look at an efficient way to just identify differences and produce some helpful statistics along with them. Along the way, I hope you learn a few useful techniques.
Setting up a test environment
We’ll need two tables to test with, so here is some simple code that will do the trick:
This code creates the tables Original and Revised that hold customer data. At the moment they are completely different, which you can see since they are small. But what if these tables had thousands or millions of rows? Eyeballing them wouldn’t be possible. You’d need a different approach. Enter set-based operations!
Set-based operations
If you remember your computer science classes, you’ll no doubt recall studying sets as mathematical objects. Relational databases combine set theory with relational calculus. Put them together and you get relational algebra, the foundation of all RDBMS’s (Thanks and hats-off to E.F. Codd). That means that we can use set theory. Remember these set operations?
A ∪ B Set union: Combine two sets into one
A ∩ B Set intersection: The members that A and B have in common
A − B Set difference: The members of A that are not in B
These have direct counterparts in SQL:
A ∪ B : UNION or UNION ALL (UNION eliminates duplicates, UNION ALL keeps them)
A ∩ B : INTERSECT
We can use these to find out some things about our tables:
Will show us what rows these two tables have in common (none, at the moment)
Will show us all the rows of the Original table that are not in the Revised table (at the moment, that’s all of them).
Using these two queries, we can see if the tables are identical or what their differences may be. If the number of rows in the first query (INERSECT) is the same as the number of rows in the Original and Revised tables, they are identical, at least for tables having keys (since there can be no duplicates). Similarly, if the results from the second query (EXCEPT) are empty and the results from a similar query reversing the order of the selects is empty, they are equal. Saying it another way, if both sets have the same number of members and all members of one set are the same as all the members of the other set, they are equal.
Challenges with non-keyed tables
The tables we are working with are keyed so we know that each row must be unique in each table, since duplicate keys are not allowed. What about non-keyed tables? Here’s a simple example:
The last query, using EXCEPT, returns an empty result. But the tables are different! The reason is that EXCEPT, INTERSECT and UNION eliminate duplicate rows. Now this query:
These are the three rows that the two tables have in common. However, since each table has 4 rows, you know they are not identical. Checking non-keyed tables for equality is a challenge I’ll leave for a future article.
Giving our tables something in common
Let’s go back to the first example using keyed tables. To make our comparisons interesting, let’s give our tables something in common:
Here, we take about half the rows of the Original table and insert them into the Revised table. Using ORDER BY NEWID() makes the selection pseudo-random.
This query takes some of the rows from the Revised table and inserts them into the Original table using a similar technique, while avoiding duplicates.
Now, the EXCEPT query is more interesting. Whichever table I put first, I should get 5 rows output. For example:
Now the two tables also have 10 rows in common:
Depending on the change being implemented, these results may be either good or bad. But at least now you have something to show for your efforts!
Row-to-row changes
So far, we’ve only considered changes in whole rows. What if only certain columns are changing? For example, what if in the Revised table, for some customer id, the name or phone number changed? It would be great to be able to report the rows that changed and also to provide a summary of the number of changes by column and also some way to see what changed between two rows, not just visually, but programmatically. These sample tables are small and narrow. Imagine a table with 40 columns, not 4 and 1 million rows, not 10. Computing such a summary would be very tedious. I’m thinking about something like this:
This shows me that there are 8 rows with the same customer id but different contents and that four of them have different phone numbers, two have different names and two have different addresses.
I’ll also want to produce a table of these differences that can be joined back to the Original and Revised tables.
Как сравнить 2 таблицы в sql
Эта статья и её продолжение появились благодаря вопросам студентов на семинарах по СУБД. Продолжение будет посвящено рекурсивным запросам CTE. Статью готовил я, Присада Сергей Анатольевич, сейчас работаю в Финансовом университете при Правительстве РФ, почта sergey.prisada на яндексе.
Часто решаемая задача по сравнению наборов данных и поиску изменений. Все это решается простым SQL. В решении используется оператор работы со множествами MINUS, который, или его аналог, есть не во всех СУБД. Решение с JOIN будет в следующей статье.
В качестве примера создадим таблицу EMPLOYEES_TEST , заполним данными и создадим её копию EMPLOYEES_TEST_BKP. Затем удалим строки из исходной таблицы, обновим некоторые и вставим новые. Пример решается в Oracle DB. Если нет доступа, регистрируйтесь на apex.oracle.com или установите бесплатную Oracle XE. Код создания таблиц ниже в листинге.
Изменим исходную таблицу.
Теперь две таблицы отличаются.
Задача: найти различающийся строки и вид CRUD операции над ними с применением SQL. Или так: какие операции выполнены над строками EMPLOYEES_TEST.
Выполним запрос и видим результат, например строки EMPLYEE_ID 300,310,320 с атрибутом OPER = I /INSERT/ (операция произведенная над строкой).
Как использовать SQL PIVOT для сравнения двух таблиц в вашей базе данных
Это может случиться очень легко. Вы адаптируете таблицу, добавляя новый столбец:
Вы продолжаете, реализуя свою бизнес-логику – абсолютно никаких проблем. Но затем, позже (возможно, в производстве), некоторые пакетные задания не выполняются, потому что они делают серьезные предположения о типах данных. А именно, предполагается, что две таблицы payments и payments_archive имеют одинаковый тип строки:
Имея один и тот же тип строки, вы можете просто переместить строку из одной таблицы в другую, например, используя такой запрос:
(не то, чтобы использование приведенного выше синтаксиса было хорошей идеей в целом, на самом деле это плохая идея. но вы поняли)
То, что вы получаете сейчас, это:
Исправление очевидно, но, вероятно, бедная душа, которая должна это исправить, – это не вы, а кто-то еще, кто должен выяснить среди, возможно, сотен столбцов, которые не совпадают. Вот как (в Oracle):
Используйте PIVOT для сравнения двух таблиц!
Конечно, вы можете не использовать PIVOT и просто выбрать все столбцы из любой таблицы из представлений словаря:
Это даст следующий результат:
Не очень читабельно. Конечно, вы можете использовать операции над множествами и применять INTERSECT и MINUS ( EXCEPT ) для фильтрации соответствующих значений. Но намного лучше
И вышесказанное теперь производит:
Теперь очень легко определить столбец, который отсутствует в таблице PAYMENTS_ARCHIVE . Как видите, результат исходного запроса дает одну строку на столбец AND на таблицу. Мы взяли этот результат и повернули его «FOR» для имени таблицы, так что теперь мы получим только одну строку на столбец
Как читать PIVOT?
Это просто. Комментарии встроены:
Вот и все. Не так сложно, правда?
Хорошая вещь в этом синтаксисе состоит в том, что мы можем сгенерировать столько дополнительных столбцов, сколько захотим, очень легко:
… Производящий (после дополнительного ошибочного DDL)…
Таким образом, мы можем обнаружить еще больше недостатков между различными типами строк таблиц. В приведенном выше примере мы использовали MAX() , потому что мы должны предоставить функцию агрегирования, даже если каждый поворотный столбец соответствует ровно одной строке в нашем примере – но это не обязательно.
Что если я не использую Oracle?
SQL Server также поддерживает PIVOT, но другие базы данных не поддерживают. Вы всегда можете эмулировать PIVOT используя GROUP BY и CASE . Следующее утверждение эквивалентно предыдущему: