SQL compare two tables - ghdrako/doc_snipets GitHub Wiki

• https://neon.com/postgresql/postgresql-tutorial/compare-two-tables-in-postgresql
• https://github.com/remysucre/blog/blob/main/posts/sql-eq.md
• https://github.com/dbt-labs/dbt-audit-helper#compare_relations-source
• https://github.com/gregw2hn/handy_sql_queries/blob/main/sql_diff.sql
• https://github.com/sqlitebrowser/dbhub.io/blob/5c9e1ab1cfe0f59b25fb78a576c0cac429323cad/common/diff.go
• https://www.sqlite.org/sqldiff.html utility:
• https://www.sqlite.org/sqldiff.html

find the rows in the foo table but not in the bar table

SELECT
  id,
  name,
  'not in bar' AS note
FROM
  foo
EXCEPT
SELECT
  id,
  name,
  'not in bar' AS note
FROM
  bar;

find difference in both side

SELECT
  id,
  name,
  'not in bar' AS note
FROM
  foo
EXCEPT
SELECT
  id,
  name,
  'not in bar' AS note
FROM
  bar
UNION
SELECT
  ID,
  NAME,
  'not in foo' AS note
FROM
  bar
EXCEPT
SELECT
  ID,
  NAME,
  'not in foo' AS note
FROM
  foo;

Comparing two tables using an outer join

SELECT
  id,
  name
FROM
  foo FULL
  OUTER JOIN bar USING (id, name)
WHERE
  foo.id IS NULL
  OR bar.id IS NULL;

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

• reply

• CHECKSUM

select * from (
    (
      select *
      from table1
      minus
      select *
      from table2
    )
    union all
    (
      select *
      from table2
      minus
      select *
      from table1
    )
  )

reply