How to check two different databases for same content of column in MySQL?

Question

I would like to know how to check in two different databases for same content in column.

On example, I have database1 with this data:

+----------+------------+------------------+
| actor_id | actor_user | actor_name       |
+----------+------------+------------------+
|        1 |     234287 | User1            |
|        2 |      47689 | User2            |
|        3 |     235133 | User3            |
|        4 |      62861 | User4            |
|        5 |     190486 | User5            |
+----------+------------+------------------+

And database2 with this data:

+----------+------------+------------------+
| actor_id | actor_user | actor_name       |
+----------+------------+------------------+
|        1 |     234257 | User5            |
|        2 |      47619 | User6            |
|        3 |     235123 | User7            |
|        4 |      62811 | User8            |
|        5 |     190436 | User9            |
+----------+------------+------------------+

I need query which will show User5 as output.

This would be a job for the INTERSECT operator, which mysql doesn't implement, but can be simulated like this https://www.mysqltutorial.org/mysql-intersect/ — , Nov 08 '20 at 23:20

GMB · Accepted Answer · 2020-11-08T23:21:47.977

Presumably, you mean table rather than database.

If you want records that have the same values in all columns of both tables, then you can join:

select t1.*
from table1 t1
inner join table2 t2
    on  t2.actor_id = t1.actor_id 
    and t2.actor_user = t1.actor_user 
    and t2.actor_name = t1.actor_name

If you want a selective match, exists might be more appropriate - say, names in table1 that exists in table2, then:

select t1.*
from table1 t1
where exists (select 1 from table2 t2 where t2.name = t1.name)

How to check two different databases for same content of column in MySQL?

1 Answers1