SQL: How to find duplicates across 2 columns-CodePudding

I would like to return either 'duplicate' or 'not duplicate' for values in column A that appear in column B within the same table. Example below:

Column A	Column B	Column C
3	1	duplicate
2	4	not duplicate
5	3	not duplicate

How would i go about this?

CodePudding user response：

We can try using exists logic here:

SELECT
    ColumnA,
    ColumnB,
    CASE WHEN EXISTS (SELECT 1 FROM yourTable t2 WHERE t2.ColumnB = t1.ColumnA)
         THEN 'duplicate' ELSE 'not duplicate' END AS ColumnC
FROM yourTable t1;

CodePudding user response：

Join is solution

   SELECT columnA, columnB, case when t2.columnB is not null then 'duplicate' else 'not duplicate' end as columnC
    FROM  table1 t1 left join table1 t2 on t1.columnA = t2.columnB