0

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.

Improve this question
0

1 Answer 1

Reset to default
2 
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

Improve this answer
7
  • 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/… Commented Aug 31, 2022 at 10:38
  • @klor This is another task. You should have either specified this from the beginning or created another question. Commented Aug 31, 2022 at 11:08
  • Sorry. You are right. But I had to revise the specification in the meantime. I edited the OP. Commented Aug 31, 2022 at 11:15
  • 1
    @klor The answer altered. Commented Aug 31, 2022 at 11:21
  • 1
    @klor MySQL does not implement FULL JOIN. But yes, you may avoid a subquery. Updated. Commented Aug 31, 2022 at 11:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.