I'm using the longuish query below. Is there a better way? There's lot of repetition in that query, which I find unappealing.

I guess two CTEs for the LHS and RHS tables might make it smaller syntactically, but would that yield the same plan?

I must use 3.36, so no access to newer right and full-outer joins, although a better query using the latest would also be interesting.

PS: Of course, replace {0} below with the name of an attached DB.

    SELECT type, name, tbl_name
      FROM sqlite_master
     WHERE name NOT LIKE 'sqlite%'
    EXCEPT
    SELECT type, name, tbl_name
      FROM {0}.sqlite_master
     WHERE name NOT LIKE 'sqlite%'

    UNION ALL

    SELECT type, name, tbl_name
      FROM {0}.sqlite_master
     WHERE name NOT LIKE 'sqlite%'
    EXCEPT
    SELECT type, name, tbl_name
      FROM sqlite_master
     WHERE name NOT LIKE 'sqlite%'

If you can't do a FULL JOIN, just union together two LEFT JOINs.

For example:

CREATE TABLE master1
(
  type TEXT,
  name TEXT,
  tbl_name TEXT
);

CREATE TABLE master2
(
  type TEXT,
  name TEXT,
  tbl_name TEXT
);

INSERT INTO master1 VALUES ( 'table', 't1', 't1' );
INSERT INTO master1 VALUES ( 'table', 'tx', 'tx' );

INSERT INTO master2 VALUES ( 'table', 't2', 't2' );
INSERT INTO master2 VALUES ( 'table', 'tx', 'tx' );

SELECT *, 'master1' AS instance
FROM
  master1
LEFT JOIN
  master2
  USING (type, name, tbl_name)
WHERE master2.type IS NULL

UNION ALL

SELECT *, 'master2' AS instance
FROM
  master2
LEFT JOIN
  master1
  USING (type, name, tbl_name)
WHERE master1.type IS NULL
;

The output of this would look something like this:

type   name  tbl_name  instance
-----  ----  --------  --------
table  t1    t1        master1 
table  t2    t2        master2 

Having access to FULL JOIN doesn't make the query significantly simpler if you want the output to not be split across multiple column sets. To get the same output using FULL JOIN you need to do something like this:

WITH
  xor AS (
    SELECT
      m1.type AS m1type, m1.name AS m1name, m1.tbl_name AS m1tbl,
      m2.type AS m2type, m2.name AS m2name, m2.tbl_name AS m2tbl
    FROM
      master1 AS m1
    FULL JOIN
      master2 AS m2
      USING (type, name, tbl_name)
    WHERE m1.type IS NULL OR m2.type IS NULL
  )
SELECT
  coalesce(m1type, m2type) AS type,
  coalesce(m1name, m2name) AS name,
  coalesce(m1tbl,  m2tbl ) AS tbl_name,
  iif(m1type IS NOT NULL, 'master1', 'master2') AS instance
FROM
  xor
;