Compare columns returned by two sub-queries for distinct values

I have two queries say, A and B.

A = SELECT number FROM table1 WHERE my_condition;
B = SELECT number FROM table2 WHERE my_condition;

Both of these result in a column which has numbers e.g.

A returns a single column: [1, 8, 3, 4, 5]
B returns a single column: [1, 7, 3, 4, 6, 2, 5]

I want to know which numbers don't exist in each other's table. So, result will be: [2, 6, 7, 8].

What I am trying is the following in MySQL Workbench 8.0:

DROP TEMPORARY TABLE IF EXISTS temp_table1;
DROP TEMPORARY TABLE IF EXISTS temp_table2;

CREATE TEMPORARY TABLE temp_table1
    SELECT number FROM table1 WHERE my_condition; /* This is my query 'A' */

CREATE TEMPORARY TABLE temp_table2
    SELECT number FROM table2 WHERE my_condition; /* This is my query 'B' */


SELECT number FROM temp_table1 WHERE number NOT IN (SELECT number FROM temp_table2)
UNION
SELECT number FROM temp_table2 WHERE number NOT IN (SELECT number FROM temp_table1);

DROP TEMPORARY TABLE temp_table1;
DROP TEMPORARY TABLE temp_table2;

This doesn't work and I get an error that "cannot reopen temporary table". I have no idea about this. Any similar possibly easier to understand solution that does work in MySQL?

Solution

You could use the following union query:

SELECT t1.number
FROM table1 t1
LEFT JOIN table2 t2 ON t2.number = t1.number
WHERE <condition> AND t2.number IS NULL
UNION ALL
SELECT t2.number
FROM table1 t1
RIGHT JOIN table2 t2 ON t2.number = t1.number
WHERE <condition> AND t1.number IS NULL

The first half of the query finds numbers unique to the first table, while the second half finds numbers unique to the second table.

Using your exact current approach with common table expressions (available from MySQL 8+), we can try:

WITH temp_table1 AS (
    SELECT number FROM table1 WHERE my_condition
),
temp_table2 AS (
    SELECT number FROM table2 WHERE my_condition
)

SELECT number FROM temp_table1 WHERE number NOT IN (SELECT number FROM temp_table2)
UNION
SELECT number FROM temp_table2 WHERE number NOT IN (SELECT number FROM temp_table1);