Return boolean if duplicates exist

How do I return True if a table has any duplicate records and False if it has none.

I don’t need a count, I don’t need to know which rows, all I want to know is if this table has duplicates or not. That’s it.

Answer

If you don’t want to list out columns, then here is one method:

select (count(*) <> num_distinct) as has_duplicates
from t cross join
     (select count(*) as num_distinct
      from (select distinct * from t) t
     ) tt;

If you have a primary key, then a more efficient method would be:

select (count(*) <> count(distinct pk)) as has_duplicates
from t;

A relatively efficient method with a primary key would be:

select (count(*) = 1) as has_duplicates
from (select t.*
      from t
      where exists (select 1 from t t2 where t2.pk = t.pk and t2.? <> t.?)
      fetch first 1 row only
     ) t;

The ? is for the column/columns that you care about in terms of duplicates.