SQL Break: how to check duplicates

Quickly Check for Duplicates using SQL

If you are writing queries, you often need to check the table or result for duplicates. This will be a short, straightforward demonstration of how to check duplicates using SQL, specifically COUNT(), COUNT(DISTINCT ) and HAVING clauses:

Example:

checking if order_ids are unique in a particular table or if there is duplicate

SELECT  COUNT(order_id) as order_count , COUNT(DISTINCT order_id) as order_distinct_count FROM database.schema.table

If order_count and order_distinct_count are different, there are duplicates. If so, you can check examples by querying the following:

SELECT  Order_id, COUNT(order_id) as counts FROM database.schema.table GROUP BY Order_id HAVING counts > 1

Then you can examine further for a few of those order_id if needed:

SELECT *  FROM database.schema.table WHERE order_id = ‘example order id you got from the previous query’

#checkduplicates #sql #sqlbreak