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;

Answer 2

 CREATE TABLE Table1 (
        Col1 INT
    );
    
     
    INSERT INTO Table1 (Col1) VALUES
        (10),
        (20),
        (20),
        (20),
        (30),
        (30),
        (40);
    
     
    CREATE TABLE Table2 (
        Col1 INT
    );
    
     
    INSERT INTO Table2 (Col1) VALUES
        (10),
        (20),
        (30),
        (40);

    select T1.col1,case when T2.col1 is null then 'NotMatched'  else 'Matched' end col2
    from 
    (
    select col1 , ROW_NUMBER() OVER (PARTITION BY t.col1   ORDER BY t.col1   ) er
    from table1 t 
    ) as T1
    left join 
    (
    select t.col1 ,count(*) er2
    from table1 t 
    group by t.col1 
    ) as T2
    on T1.col1=T2.col1 and T1.er=T2.er2
    order by 1,2
    ;

Run

Answer 3

Assuming table2 contains distinct values only, you can use row_number() to assign a unique number to each row then left join first record per partition only :

select t1.Col1, CASE WHEN t2.Col1 IS NOT NULL THEN 'Matched' ELSE 'Not Matched' END AS Col2
from (
  select *, row_number() over(partition by Col1  order by Col1 ) as rn
  from table1
) as t1
left join table2 t2  ON t1.Col1  = t2.Col1 and rn = 1;

Demo here

Answer 4

Here is the solution

select 
    a.col1 , 
    case when a.col1 = b.col1 then 'Matched'
         else 'Not Matched' end as col2
from
(
    select * , row_number() over(partition by col1 order by col1) as seq from tab1 
) as a left join (select col1 , row_number() over(partition by col1 order by col1) as seq from tab2) as b
on a.seq = b.seq and a.col1 = b.col1;