Emulating a FULL OUTER JOIN

A full outer join keeps unmatched rows from both sides, which is what reconciling two lists needs. MySQL 9.7 524 has none. Here a supplier's cost feed lacks one Databases title and lists one the shop does not stock:

A failed FULL JOIN, then LEFT JOIN plus an anti-RIGHT JOINSQL

CREATE TABLE supplier_feed (sku VARCHAR(20) PRIMARY KEY, cost DECIMAL(8,2) NOT NULL);
INSERT INTO supplier_feed VALUES ('BK-SQL-01', 26.00), ('BK-PG-01', 21.50);
SELECT * FROM products FULL JOIN supplier_feed f ON products.sku = f.sku;
SELECT p.sku AS ours, f.sku AS theirs, p.price, f.cost
FROM products AS p LEFT JOIN supplier_feed AS f ON f.sku = p.sku
WHERE p.category_id = 3
UNION ALL
SELECT p.sku, f.sku, p.price, f.cost
FROM products AS p RIGHT JOIN supplier_feed AS f ON f.sku = p.sku
WHERE p.id IS NULL;

Output

Query OK, 0 rows affected (0.040 sec)
Query OK, 2 rows affected (0.012 sec)
Records: 2  Duplicates: 0  Warnings: 0
ERROR 1054 (42S22): Unknown column 'products.sku' in 'on clause'
Warning (Code 4119): Using FULL as unquoted identifier is deprecated, please use quotes or
  rename the identifier.
Error (Code 1054): Unknown column 'products.sku' in 'on clause'
+-----------+-----------+-------+-------+
| ours      | theirs    | price | cost  |
+-----------+-----------+-------+-------+
| BK-SQL-01 | BK-SQL-01 | 44.50 | 26.00 |
| BK-SQL-02 | NULL      | 29.00 |  NULL |
| NULL      | BK-PG-01  |  NULL | 21.50 |
+-----------+-----------+-------+-------+
3 rows in set (0.001 sec)

FULL is not reserved, so MySQL read products FULL as a table alias and JOIN as an inner join, then lost products.sku under the new name; FULL OUTER JOIN is a plain syntax error. The emulation takes every left row with its match or NULLs, then appends only the right rows with no match (WHERE p.id IS NULL). Prefer it to the common LEFT JOIN ... UNION ... RIGHT JOIN: plain UNION must deduplicate everything, and it merges rows that are genuinely identical.

MENU

Emulating a FULL OUTER JOIN