How to compare two arbitrary tables?

Question

Java

Good afternoon!
There are two tables. It is known that most of the data in them intersect. But there may be completely different data or changed data in one of the tables. It is necessary to write an algorithm by which the program would find common records in tables and display them. How would you approach this problem to begin with?

Answer 1

Is it Java or SQL? smile
If SQL. I mean that Orakl is used.
First, if the tables are not large, then there should be no problems with the usual join or cartesian product

SELECT 
t1.*, t2.*
FROM 
table1 t1,
table2 t2
WHERE
t1.fields = t2.fields

If the tables are large, then you need to be more careful so that a full scan of the tables does not go and everything does not hang to hell. Make sure the table has indexes. You're lucky if the indices match the fields you want to compare against.
Further, I would "bite off" small pieces of one of the tables and join the other, either as in the first example, or as a nested subquery in SELECT. If not critical for performance, little by little would add larger chunks of the original table. Again, if it does not slow down, then ok. Otherwise, I would write a procedure for splitting into pieces and the subsequent join.

Answer 2

You can take 2 tables and try to make an INNER JOIN on the fields that should intersect.

Answer 3

Merging two tables

SELECT * FROM tbl1
UNION ALL
SELECT * FROM tbl2

then group by all fields and select those groups that contain more than one record

SELECT *
    FROM (SELECT * FROM tbl1
          UNION ALL
          SELECT * FROM tbl2
         )
    GROUP BY field1, field2, ...
    HAVING COUNT(*) > 1

Such a query does not require indexes and will be very efficient even on very large tables
Of course, the decision is based on the assumption that records in each table are unique

Answer 4

You can select columns and rows that intersect in two tables in this way:

SELECT column1, column2, column3, column4 FROM table1
intersect
SELECT column1, column2, column3, column4 FROM table2

intersect only outputs rows that are in both tables.
union outputs rows excluding duplicates (distinct). Accordingly, this is a difficult operation.
union all outputs rows from both tables without processing.
minus subtracts the lower query strings from the upper query strings. Displays only those rows that are in the top query and not in the bottom query.
All of the above operations require that the output columns be the same.
If the table structure is different, only some columns are the same, and you need to display all the columns, then you need to use Join. The join example was described by Oleg Agapov.