Joins, MySQL style: FULL OUTER emulation and multi-table writes

The SQL course covers inner and outer joins. This lesson is about the places where MySQL differs from the standard and from other databases.

No FULL OUTER JOIN

MySQL does not implement FULL [OUTER] JOIN. Writing it is a syntax error, as the first attempt further down shows. The standard emulation is two queries glued together:

1. a LEFT JOIN from A to B, which gives every row of A, matched or not;
2. UNION ALL the rows of B that have no match in A (a LEFT JOIN from B to A, keeping WHERE a.key IS NULL).

1CREATE TABLE stock   (sku VARCHAR(5) PRIMARY KEY, qty INT NOT NULL);2CREATE TABLE counted (sku VARCHAR(5) PRIMARY KEY, qty INT NOT NULL);3INSERT INTO stock   VALUES ('A', 10), ('B', 5), ('C', 0);4INSERT INTO counted VALUES ('B', 4), ('C', 0), ('D', 2);

1SELECT s.sku, s.qty AS stock_qty, c.qty AS counted_qty2FROM stock s3LEFT JOIN counted c ON c.sku = s.sku4UNION ALL5SELECT c.sku, NULL, c.qty6FROM counted c7LEFT JOIN stock s ON s.sku = c.sku8WHERE s.sku IS NULL9ORDER BY sku;

Every SKU appears once: A exists only in stock, D only in the count, B and C in both. The second half of the query is an anti-join, and its IS NULL test must be on a column that cannot be NULL in a real match, normally the join key.

Use UNION ALL, not UNION. Plain UNION removes duplicate rows, which hides genuine duplicates in your data and adds a deduplication step to do it. The anti-join already guarantees the two halves do not overlap.

UPDATE and DELETE can join

MySQL lets a data-changing statement join other tables, which is often clearer than a correlated subquery:

UPDATE products p
JOIN price_updates u ON u.product_id = p.id
SET p.price = u.new_price;

DELETE c                        -- name the table(s) to delete from
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;             -- customers with no orders

1CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(20), price DECIMAL(8,2));2CREATE TABLE price_updates (product_id INT PRIMARY KEY, new_price DECIMAL(8,2));3INSERT INTO products VALUES (1, 'Pen', 1.00), (2, 'Ink', 5.00), (3, 'Pad', 3.00);4INSERT INTO price_updates VALUES (1, 1.20), (3, 2.50), (9, 9.99);5CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(20));6CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT);7INSERT INTO customers VALUES (1, 'Ann'), (2, 'Bob'), (3, 'Cy');8INSERT INTO orders VALUES (10, 1), (11, 1), (12, 3);

1UPDATE products p2JOIN price_updates u ON u.product_id = p.id3SET p.price = u.new_price;4 5DELETE c6FROM customers c7LEFT JOIN orders o ON o.customer_id = c.id8WHERE o.id IS NULL;9 10SELECT (SELECT GROUP_CONCAT(CONCAT(name, '=', price) ORDER BY id) FROM products) AS prices,11       (SELECT GROUP_CONCAT(name ORDER BY id) FROM customers)                   AS customers_left;

The update changed the two products that have a pending price, and the delete removed the one customer with no orders. In a multi-table DELETE the names between DELETE and FROM say which tables lose rows; the others are only read.

Error 1093: reading the table you are changing

MySQL refuses an UPDATE or DELETE whose own subquery selects from the target table.

1CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(20));2INSERT INTO customers VALUES (1, 'Ann'), (2, 'Bob');3-- A helper that runs a statement and records the error instead of stopping.4CREATE TABLE attempts (n INT AUTO_INCREMENT PRIMARY KEY, statement TEXT, outcome TEXT);5CREATE PROCEDURE try_sql(IN stmt TEXT)6BEGIN7  DECLARE EXIT HANDLER FOR SQLEXCEPTION8  BEGIN9    GET DIAGNOSTICS CONDITION 1 @err_no = MYSQL_ERRNO, @err_msg = MESSAGE_TEXT;10    INSERT INTO attempts (statement, outcome)11    VALUES (stmt, CONCAT('error ', @err_no, ': ', REPLACE(@err_msg, CONCAT(DATABASE(), '.'), '')));12  END;13  SET @sql = stmt;14  PREPARE s FROM @sql;15  EXECUTE s;16  DEALLOCATE PREPARE s;17  INSERT INTO attempts (statement, outcome) VALUES (stmt, 'ok');18END;

1CALL try_sql('SELECT * FROM customers x FULL OUTER JOIN customers y ON x.id = y.id');2CALL try_sql('UPDATE customers SET name = ''Max'' WHERE id = (SELECT MAX(id) FROM customers)');3CALL try_sql('UPDATE customers SET name = ''Max'' WHERE id = (SELECT m FROM (SELECT MAX(id) AS m FROM customers) AS t)');4CALL try_sql('UPDATE customers c JOIN (SELECT MIN(id) AS m FROM customers) t ON c.id = t.m SET c.name = ''Min''');5 6SELECT n, outcome,7       IF(n = 4, (SELECT GROUP_CONCAT(name ORDER BY id) FROM customers), NULL) AS names_now8FROM attempts9ORDER BY n;

Two ways around it, both shown: wrap the subquery in a derived table (SELECT ... FROM (SELECT ...) AS t), which MySQL materialises before the update starts, or turn the statement into a multi-table UPDATE ... JOIN.

Common mistakes

• Writing FULL OUTER JOIN. It does not exist in MySQL; the attempt above fails with error 1064.
• Emulating it with UNION instead of UNION ALL. That silently collapses rows that are legitimately identical.
• Testing the anti-join on a nullable column. WHERE b.note IS NULL also keeps matched rows whose note happens to be NULL; test the key.
• Using a self-referencing subquery in DELETE. Error 1093; use a derived table or a join.

What interviewers ask

• How do you get a full outer join in MySQL? LEFT JOIN one way, UNION ALL the unmatched rows of a LEFT JOIN the other way.
• How do you delete the rows of A that have no match in B? DELETE a FROM a LEFT JOIN b ON ... WHERE b.key IS NULL, or .