SQL tip: How to check duplicates

You can check the duplicates in one table with the clause HAVING COUNT.

In the following examples I select the passports that are duplicate in the 

table tourist_table.

This first case just returns the passports that have duplicates, it does not

especify how many duplicates are for each passport.

SELECT passport FROM tourist_table
GROUP BY passport
HAVING COUNT(*) > 1

In this case the query returns the passports that have duplicates and the

number of repeated values that every one has. 

SELECT passport, COUNT(passport) FROM tourist_table
GROUP BY passport
HAVING COUNT(*) > 1

In this last case the query retunrs all the fields of the row for every 

duplicate.

SELECT * FROM tourist_table 
WHERE passport  IN 
   ( Select passport  FROM  tourist_table 
     GROUP BY passport  
    Having Count(*) > 1 )