Find duplicate values or non-duplicate values in a table
To find duplicate values or non-duplicate values in a table, we always stick to a common technique that is used very often in various database management systems, such as Oracle, SQL Server, MySQL... The key here in this technique is the use of HAVING clause with COUNT function to get the number of duplicate values. By specifying how many duplicate values we want to get in a sub-query, we can obtain non-duplicate or duplicate records from a table. Duplicates are those records that are duplicated twice or more in the table.
Queries on this page use the Oracle Northwind database that was converted from the popular Access Northwind database.
Query 1: Find duplicated values
Query below finds suppliers who sell more than one product to Northwind Trader. It uses IN operator in WHERE clause to test each value in the returned list from the subquery. In the subquery, count function is used with HAVING clause.
When using HAVING clause, consider the following facts and guidelines:
Below is a rewritten version of the query above. It uses INNER JOIN instead of IN operator. It returns the exact same result - 74 records. The IN keyword in query above can be replaced by using JOIN clause with a sub-query which produces a temporary table c.
74 records returned.
Exercise: Find suppliers who supply more than 4 products
Here is the result of finding suppliers who sell more than 4 products to Northwind Trader. Make the following change in query 1 above.
having count(*) > 4
10 records returned.
Query 2: Find non-duplicated values
Find suppliers who only sell one product to Northwind Trader.
Here is the rewritten version by using INNER JOIN.
3 records in the query result - each of the suppliers sold only one product to Northwind Trader.
Copyright © 2017 GeeksEngine.com. All Rights Reserved.
This website is hosted by HostGator.
No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.