How to compare tables?

Question

PostgreSQL

There are two tables with the same columns and they need to be compared with each other, how can this be done?

Answer 1

As far as I understand, 4 fields can match in these tables, but are there other fields?
Then you can connect them, but exactly by these fields:

SELECT *
FROM mytable1 t1, mytable2 t2
WHERE t1.field1 = t2.field1
AND t1.field2 = t2.field2

and so on for all compared fields.
Then the result will be obtained, where the row from the table mytable1 matches the row mytable2 in these specified fields.
But in general, as you have already been told, the idea of ​​doing this with different tables is fundamentally wrong. If it was necessary to separate by user, then user_id had to be made a table field, and not create a separate table for the user.

Answer 2

SELECT *
FROM table1
INTERSECT
SELECT *
FROM table2