How To Check A SQL Table For Duplicate Values
The Day One SQL Query We (Should) All Know And Love
If you’d like to support my writing, consider buying a copy of my E-Book, JetPack SQL. It’s a comprehensive, 70+ page PDF for absolute beginners. Click to learn more: https://stan.store/datawithjon/p/elevate-your-sql-skills-elevate-your-career
On day one of being a data engineer or data analyst, your manager will likely unspool a large SQL environment of database objects before you. The column names will be cryptic and overwhelming. Still, your manager will show you the trick to piecing it all together: a copy-and-paste query that identifies duplicate column values.
Equipped with this query, you will be free to roam the tabular landscape of your company’s SQL environment, identifying the underlying data models along the way.
Enough wordsmithing. Let’s look at the query and its variations. For context, assume we are working with the primary user table for an application.
Every user should have a unique id, and there should only be one row per user. The query should answer whether or not this is the case in our data.
SELECT user_id, COUNT(*)
FROM users
GROUP BY user_id
HAVING COUNT(*) > 1