Find duplicated rows between multiple same tables

Question

I have two tables with the same schema:

dbo.orders_1
| user_id | order_id | create_time             |
|---------|----------|-------------------------|
| 1       | 5        | 2018-05-04 02:26:03.808 |
| 1       | 6        | 2021-01-01 02:26:03.821 |
| 2       | 12       | 2021-12-12 02:26:03.814 |

dbo.orders_2
| user_id | order_id | create_time             |
|---------|----------|-------------------------|
| 6       | 7        | 2020-06-04 02:26:03.808 |
| 1       | 5        | 2021-01-01 02:26:03.821 |
| 3       | 4        | 2021-12-12 02:26:03.814 |

This is how I can find duplicated rows between two tables:

select 
  a.user_id, 
  a.order_id, 
  a.create_time as "create_time_1", 
  b.create_time as "create_time_2"
from 
  orders_1 a 
  join orders_2 b on a.user_id = b.user_id 
  and a.order_id = b.order_id

Which gives me this output:

| user_id | order_id | create_time_1           | create_time_2           |
|---------|----------|-------------------------|-------------------------|
| 1       | 5        | 2020-06-04 02:26:03.808 | 2021-01-01 02:26:03.821 |

It works, but now I added another table:

dbo.orders_3
| user_id | order_id | create_time             |
|---------|----------|-------------------------|
| 77      | 2        | 2015-09-15 02:26:03.808 |
| 3       | 4        | 2018-04-15 02:26:03.814 |

I want to find any duplicates in those 3 tables. What I expect is:

| user_id | order_id | create_time_1           | create_time_2           | create_time_3           |
|---------|----------|-------------------------|-------------------------|-------------------------|
| 1       | 5        | 2020-06-04 02:26:03.808 | 2021-01-01 02:26:03.821 | NULL                    |
| 3       | 4        | NULL                    | 2021-12-12 02:26:03.814 | 2018-04-15 02:26:03.814 |

What should sql look like? This is my online playground: https://sqlize.online/sql/mssql2019/7f31a47ef4c633ad8b4ba88a4276964d/

Answer 1

Usually, when finding the duplicate rows in two tables, I use a set operator INTERSECT, but that would require all the tables to contain the duplicate row.

To find the dupes in any combination of two tables, I would use UNION ALL to bring all the rows (including duplicates) into a single table expression and then find them.

Then you can use aggregation to find the duplicates, but you will lose the detail (create_time). Or use window functions to detect the dupes and keep the detail.

I've edited your playground code here.

; -- Previous statement must be properly terminated
WITH allTables
AS
(
    SELECT 'orders_1' as src, * FROM dbo.orders_1 UNION ALL
    SELECT 'orders_2' as src, * FROM dbo.orders_2 UNION ALL
    SELECT 'orders_3' as src, * FROM dbo.orders_3
)
, detectDupes
AS
(
    SELECT 
        *
        , COUNT(1) OVER (PARTITION BY t.user_id, t.order_id) AS dupeCount
    FROM allTables AS t
)
SELECT 
    *
FROM detectDupes
WHERE detectDupes.dupeCount > 1
ORDER BY 
    user_id
    , order_id
    , src

Which gives me this output

Answer 2

Another way you can accomplish your goal is just FULL JOINing all of the Tables involved, and using a WHERE clause to filter out anything that isn't a duplicate like so:

SELECT
    COALESCE(a.user_id, b.user_id, c.user_id) AS user_id,
    COALESCE(a.order_id, b.order_id, c.order_id) AS order_id,
    a.create_time AS create_time1,
    b.create_time AS create_time2,
    ISNULL(c.create_time, d.create_time) AS create_time3
FROM dbo.orders_1 AS a
FULL JOIN dbo.orders_2 AS b
    ON a.user_id = b.user_id
    AND a.order_id = b.order_id
FULL JOIN dbo.orders_3 AS c
    ON a.user_id = c.user_id
    AND a.order_id = c.order_id
FULL JOIN dbo.orders_3 AS d
    ON b.user_id = d.user_id
    AND b.order_id = d.order_id
WHERE
    (a.user_id IS NOT NULL AND b.user_id IS NOT NULL) -- A duplicate between orders_1 and orders_2
    OR (a.user_id IS NOT NULL AND c.user_id IS NOT NULL) -- A duplicate between orders_1 and orders_3
    OR (b.user_id IS NOT NULL AND d.user_id IS NOT NULL) -- A duplicate between orders_2 and orders_3

Notice there are 3 FULL JOINs, one for each Table comparison, i.e.: orders_1 to orders_2, orders_1 to orders_3 and orders_2 to orders_3.