· 5 years ago · Dec 02, 2020, 12:16 AM
1USE AdventureWorks2017;
2
3IF object_id('tempdb..#table_a', 'U') IS NOT NULL
4 DROP TABLE #table_a
5
6IF object_id('tempdb..#table_b', 'U') IS NOT NULL
7 DROP TABLE #table_b
8
9CREATE TABLE #table_a (id VARCHAR(5), seq_no INT);
10
11CREATE TABLE #table_b (id VARCHAR(5), seq_no INT);
12
13INSERT INTO #table_a
14VALUES ('a', 1), ('a', 2), ('b', 1), ('b', 2), ('b', 3), ('c', 1), ('c', 2), ('c', 3), ('c', 4), ('c', 5), ('c', 6), ('c', 7), ('c', 8), ('d', 1), ('d', 2), ('d', 3), ('d', 4), ('d', 5)
15INSERT INTO #table_b
16VALUES ('a', 1), ('b', 1), ('b', 2), ('b', 3), ('c', 1), ('c', 2), ('c', 3), ('c', 4), ('c', 5), ('c', 6), ('c', 7), ('d', 1), ('d', 2), ('d', 3), ('d', 4), ('d', 5)
17
18SELECT a.id table_a_id, a.seq_no table_a_seq, b.id table_b_id, b.seq_no table_b_seq
19FROM (
20 --get id and sequence pair for table_a
21 SELECT a.id, a.seq_no
22 FROM #table_a a
23 WHERE EXISTS (
24 --select records where ids exist in both table_a and table_b
25 SELECT 1
26 FROM (
27 SELECT DISTINCT id
28 FROM (
29 SELECT a.id
30 FROM #table_a a
31 JOIN #table_b b ON a.id = b.id
32 AND a.seq_no = b.seq_no
33 ) x
34 ) x
35 WHERE a.id = x.id
36 )
37 ) a
38FULL OUTER JOIN (
39 --get id and sequence pair for table_b
40 SELECT b.id, b.seq_no
41 FROM #table_b b
42 WHERE EXISTS (
43 --select records where ids exist in both table_a and table_b
44 SELECT 1
45 FROM (
46 SELECT DISTINCT id
47 FROM (
48 SELECT a.id
49 FROM #table_a a
50 JOIN #table_b b ON a.id = b.id
51 AND a.seq_no = b.seq_no
52 ) x
53 ) x
54 WHERE b.id = x.id
55 )
56 ) b ON a.id = b.id
57 AND a.seq_no = b.seq_no
58WHERE b.id IS NULL;
59