Custom Search
 


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:

  1. HAVING clause can only be used when a query has GROUP BY clause within it.

  2. HAVING clause is normally used together with aggregate functions such as count, sum, etc.

  3. In a query, HAVING clause must be placed immediately after GROUP BY clause.

  4. In a query, HAVING clause must be placed before an ORDER BY clause

select a.Supplier_ID, 
    b.Company_Name "Supplier Name",
    a.Product_ID, 
    a.Product_Name
from Products a
inner join Suppliers b on a.Supplier_ID=b.Supplier_ID
where a.Supplier_ID in
(
    -- Get those records where there is more than one Supplier_ID value in the products table  
    select Supplier_ID 
    from Products 
    group by Supplier_ID 
    having count(*) > 1 
)
order by a.Supplier_ID, a.Product_Name

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.

select a.Supplier_ID, 
    b.Company_Name "Supplier Name",
    a.Product_ID, 
    a.Product_Name
from Products a
inner join Suppliers b on a.Supplier_ID=b.Supplier_ID
inner join 
(
    -- Get those records where there is only one Supplier_ID value in the products table 
    select Supplier_ID 
    from Products 
    group by Supplier_ID 
    having count(*) > 1 
) c on a.Supplier_ID = c.Supplier_ID
order by a.Supplier_ID

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.

select a.Supplier_ID, 
    b.Company_Name "Supplier Name",
    a.Product_ID, 
    a.Product_Name
from Products a
inner join Suppliers b on a.Supplier_ID=b.Supplier_ID
where a.Supplier_ID in
(
    -- Get those records where there is only one Supplier_ID value in the products table 
    select Supplier_ID 
    from Products 
    group by Supplier_ID 
    having count(*) = 1 
)
order by a.Supplier_ID

Here is the rewritten version by using INNER JOIN.

select a.Supplier_ID, 
    b.Company_Name "Supplier Name",
    a.Product_ID, 
    a.Product_Name
from Products a
inner join Suppliers b on a.Supplier_ID=b.Supplier_ID
inner join
(
    -- Get those records where there is only one Supplier_ID value in the products table 
    select Supplier_ID 
    from Products 
    group by Supplier_ID 
    having count(*) = 1 
) c on a.Supplier_ID = c.Supplier_ID
order by a.Supplier_ID

3 records in the query result - each of the suppliers sold only one product to Northwind Trader.

Happy Counting!


Copyright© GeeksEngine.com



Other Recent Articles from the Database SQL category:

1.Get single records when duplicate records exist
2.How to get Top 1 record from Oracle by using PL/SQL
3.How to get Top N rows from Oracle by using SQL
4.How the data types in Access Northwind are converted to Oracle
5.How to do cross table update in Oracle
6.Export Northwind Access database to MySQL via ODBC

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.

 
Home | Feedback | Terms of Use | Privacy Policy