Join Two Table without Finding Match and Non Matching Records

Question

My table structures are shown here:

Table1

Col1
20
20
20
40
50
60

Table2

Col1
20
30
40
60

My expected result is this:

Col1	Col2
20	Matched
20	Not Matched
20	Not Matched
30	Not Matched
40	Matched
50	Not Matched
60	Matched

Query I'm trying to use:

WITH cte1 AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col1) rn
    FROM Table1
),
cte2 AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col1) rn
    FROM Table2
)
SELECT T1.Col1,
    CASE WHEN T2.Col1 IS NOT NULL THEN 'Matched' ELSE 'NotMatched' END AS Col2
FROM cte1 T1
LEFT JOIN cte2 T2
    ON T1.Col1 = T2.Col1 AND T1.rn = T2.rn
ORDER BY T1.Col1, T2.Col1 DESC;

Answer 1

You are halfway there. To identify matches and mismatches from either side of the join, you need to perform a FULL OUTER JOIN and the check for both T1.Col1 IS NOT NULL AND T2.Col1 IS NOT NULL in your CASE expression. To include Col1 in your results for all cases, you can use ISNULL(T1.Col1, T2.Col1) AS Col1.

WITH cte1 AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col1) rn
    FROM Table1
),
cte2 AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col1) rn
    FROM Table2
)
SELECT
    ISNULL(T1.Col1, T2.Col1) AS Col1,
    ISNULL(T1.rn, T2.rn) AS rn,
    CASE WHEN T1.Col1 IS NOT NULL AND T2.Col1 IS NOT NULL
        THEN 'Matched'
        ELSE 'NotMatched'
        END AS Col2
FROM cte1 T1
FULL OUTER JOIN cte2 T2
    ON T1.Col1 = T2.Col1 AND T1.rn = T2.rn
ORDER BY Col1, rn

Results:

Col1	rn	Col2
20	1	Matched
20	2	NotMatched
20	3	NotMatched
30	1	NotMatched
40	1	Matched
50	1	NotMatched
60	1	Matched

See this db<>fiddle.

Answer 2

Try

select t1.col1,
    case (select count(1) from cte2 t2 where t2.col1 = t1.col1 and t2.rn = t1.rn)
    when 1 then 'Matched'
    else 'Not matched'
    end as col2
from cte1 t1
union 
select t2.col1, 'Not matched' from cte2 t2
where not exists (select 1 from cte1 t1 where t1.col1 = t2.col1);

Returns

20  Matched
20  Not matched
30  Not matched
40  Matched
50  Not matched
60  Matched