MySQL query: Detect different rows between two tables and show differences in result table

Question

I have two tables in a MySQL database.

Table1
a_id
1
3
4
5
6
Table2
b_id
1
2
3

I would like to spot the missing row from Table1 a_id 2, and missing rows from Table2 b_id 4,5,6, which should be shown in columns deleted_from_a, deleted_from_b with values 1.

Expected output:
+----+----+--------------+--------------+
|a_id|b_id|deleted_from_a|deleted_from_b|
| 1| 1| 0| 0|
| | 2| 1| 0|
| 3| 3| 0| 0|
| 4| | 0| 1|
| 5| | 0| 1|
| 6| | 0| 1|
+----+----+--------------+--------------+

How can I query the output result?

EDIT1:

Updated Table1, to have additional values compared to Table2.

EDIT2:

Updated the specification, it was not perfect at the beginning.

Answer 1

SELECT Table1.a_id,
 Table2.b_id,
 Table1.a_id IS NULL deleted_from_a,
 Table2.b_id IS NULL deleted_from_b
FROM ( SELECT a_id AS id FROM Table1
 UNION
 SELECT b_id FROM Table2 ) total
LEFT JOIN Table1 ON total.id = Table1.a_id
LEFT JOIN Table2 ON total.id = Table2.b_id
ORDER BY total.id

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=dc08079c1868673e586f946d1f662fa0

---

Is it possible to avoid nested subquery, just use joins? – klor

SELECT t1.id a_id, t2.id b_id, 0 deleted_from_a, t2.id IS NULL deleted_from_b
FROM t1 
LEFT JOIN t2 ON t1.id = t2.id
UNION ALL
SELECT t1.id, t2.id, t1.id IS NULL, 0 
FROM t2 
LEFT JOIN t1 ON t1.id = t2.id
WHERE t1.id IS NULL
ORDER BY COALESCE(a_id, b_id)

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=6c9d5378dbc4821b8e559003c73b62f8

• klor: Thanks! If there are additional values in Table1, which are missing from Table2, then those are missing from the output. See this example: dbfiddle.uk/…
• Akina: @klor This is another task. You should have either specified this from the beginning or created another question.
• klor: Sorry. You are right. But I had to revise the specification in the meantime. I edited the OP.
• Akina: @klor The answer altered.
• Akina: @klor MySQL does not implement FULL JOIN. But yes, you may avoid a subquery. Updated.