如何识别以id1、id2关联的两个数据集中无法匹配的数据？

Alright, let's break this down step by step. The key here is that we're matching records using both id1 and id2 together—a single field match isn't enough for a valid association. Here's how to identify the unmatchable records:

1. Unmatchable Records from Dataset2 (no corresponding pair in Dataset1) #

These are rows in Dataset2 where the combined (id1, id2) value doesn't appear anywhere in Dataset1:

• Row: 2 1 52 f → The pair (2, 1) has no match in Dataset1
• Rows: 121 122 41 f and 121 122 44 m → The pair (121, 122) has no match in Dataset1
• Row: 4 221 56 m → The pair (4, 221) has no match in Dataset1

2. Unmatchable Records from Dataset1 (no corresponding pair in Dataset2) #

These are rows in Dataset1 where the combined (id1, id2) value doesn't appear anywhere in Dataset2:

• Row: 2 121 no 1 → The pair (2, 121) has no match in Dataset2
• Row: 3 122 yes 2 → The pair (3, 122) has no match in Dataset2

Using SQL to Automatically Identify Unmatched Records #

If you're working with a database, you can use these queries to quickly find unmatchable rows without manual checking:

Find unmatched rows from Dataset1:

SELECT d1.*
FROM Dataset1 d1
LEFT JOIN Dataset2 d2 ON d1.id1 = d2.id1 AND d1.id2 = d2.id2
WHERE d2.id1 IS NULL;

This returns the two rows from Dataset1 that have no matching (id1, id2) pairs in Dataset2.

Find unmatched rows from Dataset2:

SELECT d2.*
FROM Dataset2 d2
LEFT JOIN Dataset1 d1 ON d2.id1 = d1.id1 AND d2.id2 = d1.id2
WHERE d1.id1 IS NULL;

This returns the four rows from Dataset2 with no matching pairs in Dataset1.

Get unique unmatched (id1, id2) pairs (if you only need the combinations, not full rows):

For Dataset1's unmatched pairs:

SELECT id1, id2 FROM Dataset1
EXCEPT
SELECT id1, id2 FROM Dataset2;

Result: (2,121), (3,122)

For Dataset2's unmatched pairs:

SELECT id1, id2 FROM Dataset2
EXCEPT
SELECT id1, id2 FROM Dataset1;

Result: (2,1), (121,122), (4,221)

内容的提问来源于stack exchange，提问作者Bikram Adhitya Adhikari