Find duplicate values or non-duplicate values in Oracle

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:

HAVING clause can only be used when a query has GROUP BY clause within it.
HAVING clause is normally used together with aggregate functions such as count, sum, etc.
In a query, HAVING clause must be placed immediately after GROUP BY clause.
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.

Suppliers who sell more than one product.

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.
Suppliers who sell more than four products.

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.

Suppliers who sell one product.

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

	Custom Search