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