Need a SQL Query for Getting matched and not matched Records

Question

My table structures are shown here:

Table1

Col1
10
20
20
20
30
30
40

Table2

Col1
10
20
30
40

My expected result is this:

Col1	Col2
10	Matched
20	Matched
20	Not Matched
20	Not Matched
30	Matched
30	Not Matched
40	Matched

Query I'm trying to use:

SELECT 
    T1.Col1,
    CASE
        WHEN T2.Col1 IS NOT NULL THEN 'Matched'
        ELSE 'NotMatched'
    END AS Col2
FROM
    Table1 T1
LEFT JOIN 
    Table2 T2 ON T1.Col1 = T2.Col1;

Answer 1

We can stick with your join approach, but use ROW_NUMBER to keep track of occurrences:

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;