How to check if two SQL tables are the same
github.com
github.com
https://www.sqlite.org/sqldiff.html
By default it compares whole databases, but it can be told to only compare a specific table in each:
sqldiff -t mytable database1.sqlite database2.sqlite> why isn't it a standard feature of SQL to just compare two tables?
Not enough people have complained about needing it (it doesn't hit the desired ROI for a PM to prioritize the feature request).
SQLite's SQL Diff I first came across years ago - super useful. It's the perfect example of the industries which pop up to fill in the gaps that huge software vendors like Microsoft leave open. I used to work at a company valued at hundreds of millions of dollars, which made nothing but such products filling gaps.
So it's really a question of why SQL, the language, doesn't come with a table comparison command so that you can write statements akin to "SELECT * FROM (COMPARE table1 table2) AS diff WHERE ..."
I mean it's not the SQL language itself which sits in the committe specifying new features, it would be representatives from Oracle, Microsoft, IBM and similar.. Right?
This is roughly the same problem. The feature exists, but it's rare enough that the only people that need it are programmers/DBAs that need to deep-dive into a specific issue. Regular people/applications will never need this feature, so why should it be baked in the core language? The specialists already have specialist tooling, so it makes much more sense to implement this as a toolkit feature than a language feature.
> Just use sqldiff
sqldiff is sensitive to ordering, e.g., it'll say the relation [1, 2] is different from [2, 1] (I consider them to be the same because they are the same multiset). You'd need to sort with ORDER BY first, but that also requires listing all attributes explicitly like the GROUP BY solution (ORDER BY * doesn't work).
> What about CHECKSUM
It's also sensitive to ordering, and I was told different tables can have the same CHECKSUM (hash collisions?).
> Are the tables the same if they only differ by schema?
I'd say no. Perhaps a better definition of "the same" is that all SQL queries (using "textbook" SQL features) return the same result over the tables, if you just replace t1 with t2 in the query. Wait, but how do you know if the results are the same... :)
> There are better ways to compare tables
Absolutely, my recursive query runs in time O(N^N) so I'm sure you can be a little better than that.
https://github.com/sqlitebrowser/dbhub.io/blob/5c9e1ab1cfe0f...
The code there can also output a "merge" object out of the differences too, in order to merge the differences from one database object into another.
to elaborate: I'm rapidly appreciating that SQL is a "bondage and discipline" language where it is impossible for SQL to fail at any given task, it can only be failed by its users/developers.
edit: further, it occurs to me also that SQL hates you because in a sane language you'd be able to write up some generic method that says "compare every element of these collections to each other" that was reusable for all possible collections. But try defining a scalar user-defined function that takes two arbitrary resultsets as parameters. But not only can I not do that, I'm a bad person for wanting that because it violates Relational Theory.
Some links in my comment: https://news.ycombinator.com/item?id=36911937
It doesn't violate relational theory at all -- it merely violates the query compiler's requirements about what must be known at the time of query compilation.
You can absolute write a query that does what you want (you need a stored procedure that generates ad-hoc functions based on the involved tables' signatures), but stored sql objects must have defined inputs and outputs. What you're asking for is a meta-sql function that can translate to different query plans based on its inputs, and that's not allowed because its performance cannot be predetermined.
It's actually still a useful definition! (Assuming we're talking about all deterministic SQL queries and can define precisely what we mean by that!)
It's a useful definition because it includes all possible aggregations as well, including very onerous ones like ridiculous STRING_AGG stuff. Those almost certainly be candidates for reasonable queries to solve your original problem, but they are useful in benchmarking whether a proposed solution is accurate.
1) Check the record length of the two tables given your RDBMS API.
2) Check the column headers and data types of the tables are the same given your RDBMS API.
3) Only then would I export all triggers associated with the given tables using the RDBMS API and then compare that code using an external SQL language aware diff tool that excludes from comparison white space, comments, and other artifacts of your choosing.
4) Then finally if all else is the same I would export each table as CSV format and then compare that output in a CSV language aware diff tool.
This can all be done with shell scripts.
select * from (
(
select *
from table1
minus
select *
from table2
)
union all
(
select *
from table2
minus
select *
from table1
)
)Asking because I've seen that before in some software, where it tries to "keep things simple" by default. But that behaviour can be toggled off so it shows the full schema (and data) for those with the need. :)
The manufacturer is just really incompetent.
I was told their reason when asked was „it was easier (for us)“.
That's not all that unusual when something gets implemented, as people tend to take the easy approach for things that meet the desired goal.
It just sounds like the spec they were writing to wasn't very clear or it was just a checkbox list of features provided to them by marketing. So "lets get this list done then ship it". ;)
Even a couple minutes of extra debugging takes longer than learning how to add a synthetic primary.
Also in a more general case you might be comparing tables that may contain the same data but have been constructed from different sources. Or perhaps a distributed dataset became disconnected and may have seen updates in both partitions, and you have brought them together to compare to try decide which to keep or if it is worth trying to merge. In those and other circumstances there may be a key but if it is a surrogate key it will be meaningless for comparing data from two sets of updates, so you would have to disregard it and compare on the other data (which might not include useful candidate keys).
https://stackoverflow.com/questions/62735776/what-is-the-poi...
It happens a lot when people are implementing something quick and often happens in linking tables.
I can imagine that you want to have duplicates rows in a logging. If some events happens twice - you definitely want to log it twice.
I handle this by having a guid field for a primary key on such tables where there isn't a naturally unique index in the shape of the data. So something is unique, and you can delete or ignore other rows relative to that. (Just don't make your guid PK clustered; I use create-date or log-date for that.)
Trying to delete duplicates (but leave 1 behind) is tricky in itself. I recorded notes on it one time here — using “row_number()” to act as the iniquitie, https://til.secretgeek.net/sql_server/delete_duplicate_rows....
More generally (and formally) speaking, multisets violate normalization. Either you add information to the primary key to identify the copies or you roll the copies up into a quantity field. I can't think of any kind of data where neither of these would be good options.
Look, Im not trying to win the argument. In most cases you definitely right, my point is that sometime you have to work with working legacy code/system, and sometime this system could have some unique features.
And ensuring you have a real primary key should only be good for performance, in the realm of SQL databases.
The duplicate row issue is part of why I don't use MINUS for table value comparisons, nor RECURSIVE like the original article suggests (which is not supported in all databases and scarier for junior developers)... You can accomplish the same thing and handle that dupes scenario too, with just GROUP BY/UNION ALL/HAVING, using the following technique:
https://github.com/gregw2hn/handy_sql_queries/blob/main/sql_...
It will catch both if you have 1 row for a set of values in one table and 0 in another... or vice-versa... or 1 row for a set of values in one table and 2+ (dupes) in another.
I have compared every row + every column value of billion-row tables in under a minute on a columnar database with this technique.
Pseudocode summary explanation: Create (via group by) a rowcount for every single set of column values you give it from table A, create that same rowcount for every single set of column values from table B, then compare if those rowcounts match for all rows, and lets you know if they don't (sorted to make it easier to read when you do have differences). A nice fast set-based operation.
Bing Chat says: > The MINUS operator is not supported in all SQL databases. It can be used in databases like MySQL and Oracle. For databases like SQL Server, PostgreSQL, and SQLite, use the EXCEPT operator to perform this type of query
That is a nice tool to do it cross database as well.
I think it's based on checksum method.
Data Diff has two algorithms implemented for diffing in the same database and across databases. The former is based on JOIN, and the latter utilizes checksumming with binary search, which has minimal network IO and database workload overhead.
SELECT count(q.*)
FROM (SELECT a, b FROM table_a a
NATURAL FULL OUTER JOIN table_b b
WHERE a IS NOT DISTINCT FROM NULL
OR b IS NOT DISTINCT FROM NULL) q;
The key for this in SQL is `NATURAL FULL OUTER JOIN` (and row values).
I find the idea of comparing two SQL tables weird / pointless in general. Maybe OK, if implemented with some very restrictive and well-defined semantics, but I wouldn't rely on a third-party tool to do something like this.
And after that, it naturally excalates quickly :)
Never though I'd ever see SQL code golf, but here we are.
https://sqlundercover.com/2018/12/18/quickly-compare-data-in...
Also, checksum/ checksum_agg do not seem like SQL standard functions. referring https://www.postgresql.org/docs/current/features.html and https://en.wikipedia.org/wiki/SQL:2023#New_features.
And by “remember” I mean I wrote it down here so that I wouldn’t have to - https://til.secretgeek.net/sql_server/bulk_comparison_with_h...
Sometimes you want them to be equal, sometimes you want to know they aren’t… but it’s not out of the ordinary at all to want to check
I don’t think this article is expressing any opinions on normalization
"How to Check 2 SQLite Tables Are the Same"
I think SQLite's great, but "fully featured SQL engine" is not one of them. More like "perfectly adequate SQL engine" in an astoundingly compact operating envelope.
I suppose there are some other edges, like if you're storing floats, but that's no so different no matter what technique you wind up with.
What you probably want to look at is homomorphic hashing. This is usually implemented by hashing each row to an element of an appropriate abelian group and then using the group operation to combine them.
With suitable choice of group, this hash can have cryptographic strength. Some interesting choices here are lattices (LtHash), elliptic curves (ECMH), multiplicative groups (MuHash).
That indeed is a major flaw. You have to use another commutative operation that doesn’t destroy entropy. Addition seems a good choice to me.
> And even without multiple copies of rows, you can force any hash you'd like
I don’t see how that matters for this problem.
Maybe you have a trusted table hash but only a user-supplied version of the table. Before you use that data for security sensitive queries, you should verify it hasn't been modified.
Basically, if you ever have to contend with a malicious adversary, things are more interesting as usual. If not, addition is likely fine (though 2^k copies of a row now leave the k lowest bits unchanged).
https://www.timestored.com/jq/online/?qcode=t1%3A(%5B%5D%20s...
You could do it without the inner hashing, but that seems more likely to exhaust memory if your rows are big enough, since you're basically hashing an entire table at that point and that's large string in memory.
I'm sure there's some relevant papers on the simplest way to achieve this I can and should look up. Hopefully they don't summarize as it being a much harder problem to do right. ;)
Once you set up "CSV rules" then you won't need to find and replace anything, CSV is perfectly sufficient by itself.
--------------------------
Fruit | Flies Fast
Fruit Flies | Fast
SAS COMPARE Procedure Example 1: Producing a Complete Report of the Differences
proc compare base=proclib.one compare=proclib.two printall;
_____
DataComPy (open-source python software developed by Capital One)
DataComPy is a package to compare two Pandas DataFrames. Originally started to be something of a replacement for SAS’s PROC COMPARE for Pandas DataFrames with some more functionality than just Pandas.DataFrame.equals(Pandas.DataFrame) (in that it prints out some stats, and lets you tweak how accurate matches have to be).
from io import StringIO
import pandas as pd
import datacompy
compare = datacompy.Compare(
df1,
df2,
join_columns='acct_id', #You can also specify a list of columns
abs_tol=0, #Optional, defaults to 0
rel_tol=0, #Optional, defaults to 0
df1_name='Original', #Optional, defaults to 'df1'
df2_name='New' #Optional, defaults to 'df2'
)
compare.matches(ignore_extra_columns=False)
# False
# This method prints out a human-readable report summarizing and sampling differences
print(compare.report())
It's kind of like PyPI/DockerHub for SQL. Lots of cool stuff in there...here's link to the package hub: https://hub.getdbt.com/
- which columns were only in the left, or only in the right
- and then show which rows are only in the left, or only on the right
- and then for rows that are in both but have some cell differences show a set that shows those differences. (Hard to explain how this was done… it was concise but rich with details.)
This kind of “thorough” comparison was very useful for understanding the differences all at once.
Sure, but there are plenty of poorly designed databases out there!
It also optionally masks index and constraint names, which might be auto-generated and not relevant to the comparison.
[0] : https://github.com/ameensol/merkle-tree-solidity/blob/master...
Lots of otherwise simple things get complicated with duplicates, e.g. try to change or delete one row out of a set of duplicates.
A table with duplicates is not even a relation, since relations are defined as sets. SQL is in a weird place because it technically allows duplicates, but many operations are not able to distinguish between duplicates.
2. Export the query results to a .csv file(s).
3. Utilize Go along with the encoding/csv package to process each CSV row. Construct an in-memory index mapping each entity ID to its byte offset within the CSV file.
4. Traverse the CSV again, using the index to quickly locate and read corresponding lines into in-memory structures. Convert the aggregated JSON columns to standard arrays or objects.
5. After comparing individual CSV rows, save the outcomes to a map. This map associates each entityID with metadata about columns that don't match.
6. Convert the mismatch map into JSON format for further processing or segmentation.
For full comparisons of MSSQL tables, I often use Visual Studio which has a Data Comparison tool that shows identical rows and differences between source and target.
A neat pithy SQL trick
What I got:
A lesson in the Dark Arts from a wizard
It is interesting, but not something you should use. It scales horribly with number of columns