1

I have two all the similar fields tables:

table_1: 
field_1, field_2, field_3, field_4
 

table_2: 
field_1, field_2, field_3, field_4

Here field_1 can be used as foreign key to join both tables.

I would like to get all the rows from table_1 and table_2 that have at least one row in table_1 but more than one in table_2, or vice versa.

So far I have tried these related solutions:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Create Explorer
  • 357
  • 2
  • 20

2 Answers2

2

Assuming both tables have the same row type: all the same column names and types (at least compatible), you can work with row types to simplify:

SELECT (t).*
FROM  (SELECT t, count(*) AS ct1 FROM table_1 t GROUP BY 1) t1
JOIN  (SELECT t, count(*) AS ct2 FROM table_2 t GROUP BY 1) t2 USING (t)
WHERE t1.ct1 > 1
   OR t2.ct2 > 1;
  1. Group duplicates and remember the count in each table.
  2. Join the two tables, which removes all rows without match in the other table.
  3. Filter rows where at least one side has more than one copy.
  4. In the outer SELECT decompose the row type to get columns as usual.

I don't return row counts. If you need those, add ct1 and ct2 in the outer SELECT.

This requires every column type to support the equality operator =.
A prominent example that does not is json. (But jsonb does.) See:

If you have such columns, cast to text to work around it. Or you can work with hash values - which also helps performance for very wide rows and/or many duplicates. Related:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
1

One way of getting all records from table_1 which have more than one matching record in table_2 is to count the number of matching records in a subquery, and put a condition on it:

SELECT * 
FROM table_1 t1 
WHERE (SELECT count(*) 
       FROM table_2 t2 
       WHERE t1.field_1 = t2.field_1) > 1

If you're looking to have both sides of this in one query, you can combine them with a UNION:

SELECT * 
FROM table_1 t1 
WHERE (SELECT count(*) 
       FROM table_2 t2 
       WHERE t1.field_1 = t2.field_1) > 1
UNION
SELECT * 
FROM table_2 t2 
WHERE (SELECT count(*) 
       FROM table_1 t1 
       WHERE t1.field_1 = t2.field_1) > 1
Blue Star
  • 1,932
  • 1
  • 10
  • 11
  • This returns duplicates where there is more than one copy in both tables. Also, executing a correlated subquery for every row is pretty expensive for bigger tables. – Erwin Brandstetter Sep 30 '21 at 23:07
  • The language used in the question suggested to me that returning duplicates is the desired behaviour, but your answer is definitely better than mine. – Blue Star Sep 30 '21 at 23:15
  • Then I got it backwards. (But it would make more sense to just report a row count in this case ...) – Erwin Brandstetter Sep 30 '21 at 23:16