FULL OUTER JOIN Works on PostgreSQL and SQLite and Fails on MySQL and MariaDB

Matching two lists, such as invoices and payments, to see what is missing on either side calls for a FULL OUTER JOIN. PostgreSQL and SQLite run it, and MySQL and MariaDB refuse it.

MySQL and MariaDB have LEFT and RIGHT joins only. SQLite added RIGHT and FULL joins in version 3.39.

What Each Engine Answered

On a table made with

CREATE TABLE a (x INTEGER);
CREATE TABLE b (x INTEGER);
INSERT INTO a VALUES (1), (2);
INSERT INTO b VALUES (2), (3);

the example

SELECT a.x, b.x FROM a FULL OUTER JOIN b ON a.x = b.x;

gave these answers:

These answers need SQLite 3.39 or later.

What to Write Instead

This form answers the same on every engine:

SELECT a.x, b.x FROM a LEFT JOIN b ON a.x = b.x UNION ALL SELECT a.x, b.x FROM b LEFT JOIN a ON a.x = b.x WHERE a.x IS NULL;

Combine a LEFT JOIN with the rows of the other table that found no match: ... FROM a LEFT JOIN b ON a.x = b.x UNION ALL SELECT ... FROM b LEFT JOIN a ON a.x = b.x WHERE a.x IS NULL.

What BareSQL Portable Reports

Saved as example.sql, the example gets this from baresql lint --all:

$ baresql lint --all example.sql
example.sql:5:24: error: FULL OUTER JOIN works on SQLite and PostgreSQL; MySQL and MariaDB refuse it. [full-outer-join]
    FULL OUTER
    Combine a LEFT JOIN with the rows of the other table that found no match: ... FROM a LEFT JOIN b ON a.x = b.x UNION ALL SELECT ... FROM b LEFT JOIN a ON a.x = b.x WHERE a.x IS NULL.

1 problem: 1 error.

baresql rules full-outer-join prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.