This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and expected output to perform the INTERSECT ALL between the given two tables. SELECT all the common records.
Input Data for Table1:
|
1 2 3 4 5 6 |
C1 C2 ----------- ----------- 1 1 1 1 2 2 3 3 |
Input Data for Table2:
|
1 2 3 4 5 |
C1 C2 ----------- ----------- 1 1 1 1 3 3 |
Expected Output:
|
1 2 3 4 5 |
C1 C2 ----------- ----------- 1 1 1 1 3 3 |
Create two sample table with data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
CREATE TABLE table1 ( C1 INT ,C2 INT ) GO CREATE TABLE table2 ( C1 INT ,C2 INT ) GO INSERT INTO table1 VALUES (1, 1), (1, 1), (2, 2), (3, 3) INSERT INTO table2 VALUES (1, 1), (1, 1), (3, 3) GO |
Wrong solution with only INTERSECT:
|
1 2 3 4 5 6 7 8 |
SELECT c1,c2 FROM table1 INTERSECT SELECT c1,c2 FROM table2 c1 c2 ----------- ----------- 1 1 3 3 |
Correct solution: Prepare manual INTERSECT ALL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
WITH CTE AS ( SELECT ROW_NUMBER() OVER (PARTITION BY C1, C2 ORDER BY (SELECT 0)) AS RowNumber ,C1 ,C2 FROM table1 INTERSECT SELECT ROW_NUMBER() OVER (PARTITION BY C1, C2 ORDER BY (SELECT 0)) AS RowNumber ,C1 ,C2 FROM table2 ) SELECT C1, C2 FROM CTE ORDER BY 1 |
Please try the different solution for this puzzle and share it via comment...
