Identifying duplicate email records in Oracle AI Database 26ai using SQL
An example of data quality using Oracle FreeSQL, SQL normalisation and duplicate detection along with synthetic testing.
Oracle FreeSQL
Oracle Human Resources (HR) Example
October 19th 2026
Introduction
Duplicates are a common issue when trying to ensure quality data within databases.
An example of this is when there are different values which represent the same data.
For example we may have the following two values:
john@example.com
JOHN@EXAMPLE.COM
Whilst they are different in terms of uppercase and spacing, in a business application we may want them to be seen as duplicates.
We may not get the expected results by just comparing them using SQL.
In this example we will focus on identifying potential duplicate email records in Oracle AI Database 26ai using SQL.
We will use Oracle FreeSQL, the public HR example and synthetic testing data.
This example provides an insight into how we can normalise, aggregate and conditionally report on data to help us carry out real world data quality checks.
- Data Quality Challenge
Within any application, we may have different ways of entering email addresses for employees, customers or accounts.
Some may have uppercase characters and some may have unnecessary spaces.
Without normalising these values we may not recognise that there are records that need to be reviewed.
Having duplicate email addresses can cause issues such as:
Confusion with identifying users within the application.
Incorrect number of unique contacts.
Repeated alerts.
Issues with reconciliation between systems.
Unexpected results when migrating/integrating data.
However, not all applications will have duplicate values and we want to ensure that our results can be validated against business requirements.
- Oracle FreeSQL Lab
The example has been carried out using Oracle FreeSQL and using the Oracle AI Database 26ai worksheet.
We have used the public Oracle HR example and are using read only SQL statements.
Our source table is:
HR.EMPLOYEES
And the email field is:
EMAIL
We did not need to make any changes to our employee records, create any objects in production or access any sensitive enterprise data.
Our source SQL and evidence can be found in a public GitHub repo to ensure we can recreate our example.
- Normalising Email Addresses (Oracle SQL)
The key concept in this example is:
UPPER(TRIM(email))
Using two functions within Oracle SQL.
TRIM()
The TRIM() function will remove any standard leading and trailing spaces from a string. If no other characters are specified.
For example:
SELECT TRIM(' john@example.com ') AS cleaned_email
FROM dual;
Will give the result:
john@example.com
UPPER()
The UPPER() function will convert letters to uppercase.
For example:
SELECT UPPER('john@example.com') AS normalized_email
FROM dual;
Will give the result:
JOHN@EXAMPLE.COM
Combining Both
SELECT UPPER(TRIM(' john@example.com ')) AS normalized_email
FROM dual;
Will give the result:
JOHN@EXAMPLE.COM
This can be applied to our email field within our application.
Please note that this is just an example of how we can match values. In reality we may need to consider if our application requires case sensitive emails, Unicode, internationalised emails and normalisation.
- Identifying Duplicate Email Groups
Once we have normalised our values we can now identify any records that have duplicate values.
A basic query will be:
SELECT
UPPER(TRIM(email)) AS normalized_email,
COUNT() AS record_count
FROM hr.employees
WHERE email IS NOT NULL
GROUP BY UPPER(TRIM(email))
HAVING COUNT() > 1
ORDER BY record_count DESC;
This will:
Trim the values removing any leading/trailing standard spaces.
Standardise the case using the UPPER() function.
Group any values that are the same.
Count how many records there are in each group using:
COUNT()
Only show groups that have more than one record using:
HAVING COUNT() > 1
If there are any values returned then these will be potential duplicates to check.
As this will show us the email addresses we only want to do this if we are allowed to view them. In our full project we have used a different method to report.
- Privacy Focused Duplicate Audit
The query used in our lab was designed to not return any actual email addresses.
Instead we have calculated:
Duplicate Groups
Total Employees Affected
Extra Duplicate Rows
Our core query for the published lab is:
WITH normalized_emails AS (
SELECT
UPPER(TRIM(email)) AS normalized_email
FROM hr.employees
WHERE email IS NOT NULL
),
duplicate_groups AS (
SELECT
normalized_email,
COUNT() AS group_size
FROM normalized_emails
WHERE normalized_email IS NOT NULL
GROUP BY normalized_email
HAVING COUNT() > 1
),
summary AS (
SELECT
COUNT(*) AS duplicate_group_count,
NVL(SUM(group_size), 0) AS affected_employee_count,
NVL(SUM(group_size - 1), 0) AS extra_duplicate_rows
FROM duplicate_groups
)
SELECT
duplicate_group_count,
affected_employee_count,
extra_duplicate_rows AS issue_count,
CASE
WHEN duplicate_group_count = 0 THEN 'PASS'
ELSE 'REVIEW'
END AS status
FROM summary;
This uses Common Table Expressions (CTEs) to break down our logic.
Normalise our values.
Identify any duplicate groups and how many are in each group.
Calculate our metrics.
Return a concise data quality report.
We have used the NVL() function to ensure that if there are no duplicate groups, we will get a 0 rather than a NULL.
We have also used a CASE statement to classify our result as either a PASS or REVIEW.
We have been conservative with our results and if we receive a REVIEW result we cannot assume that our application data is violating any business rules.
- Using Oracle HR Example
The first time we ran our query we used the public Oracle HR example.
The results from our read only audit were:
Metric
Result
Duplicate Groups
0
Total Employees Affected
0
Extra Duplicate Rows
0
Status
PASS
As we have implemented our normalisation rule there were no duplicate groups in the sample HR data.
Whilst this is a good negative test, it doesn't show that our query will identify duplicates.
We therefore needed another test.
Screenshot of original Oracle FreeSQL output showing our audit of the public HR example with status PASS.
- Using Synthetic Duplicate Records
We ran a positive test using 6 fictional email records.
Of which we had:
2 duplicate groups that would become the same once case and spaces were normalised.
1 unique email.
1 NULL.
Our synthetic query used inline SQL data so we didn't make any changes to the HR example tables.
The results were:
Metric
Result
Total Synthetic Records
6
Duplicate Groups
2
Total Affected
4
Extra Duplicate Rows
2
Status
REVIEW
Why did we have 4 total affected?
We had 2 duplicate groups with 2 records each.
Therefore:
2 duplicate groups × 2 records = 4 affected records
To calculate our extra duplicate rows we take 1 record from each group.
Group 1: 2 records - 1 = 1 extra row
Group 2: 2 records - 1 = 1 extra row
Total extra rows = 2
Therefore our query can identify potential duplicates even if they are just different in terms of case or spaces.
Screenshot of original Oracle FreeSQL synthetic test showing 2 duplicate groups, 4 total affected and status REVIEW.
- Comparing Both Tests
Our two tests in the lab gave us some good contrasting results.
Metric
Public HR Example
Synthetic Duplicate Data
Duplicate Groups
0
2
Total Affected
0
4
Status
PASS
REVIEW
Our first test shows us what will happen if there are no duplicate groups found. Our second test shows us what happens if we have intentionally added duplicate looking records.
It is useful to run both tests. We cannot assume that our query will work correctly if we get no results.
By carrying out a controlled positive test we can easily validate that our query is doing what we expect.
Both results can be found in our evidence folder within our GitHub project.
- Real World Considerations
Whilst an email duplicate audit can help with overall data quality there are some considerations to make.
Read Only Diagnostic
We should first identify and report the records that need to be reviewed. We shouldn't automatically delete/change any records without HAVING valid business requirements and an approved remediation process.
Privacy
Email addresses can contain personal data.
We may prefer to report aggregated results rather than full email addresses. If we do need to review records at a detailed level we should ensure this is only accessible by authorised individuals.
Business Rules
Just because 2 records have the same email address, doesn't mean they are duplicates. An application may allow multiple accounts to have the same organisational email address.
The business owner will need to decide if they are genuine.
Performance
Applying the UPPER(TRIM()) function to a large amount of records can cause extra overhead. If this query needs to be run regularly in production we should consider the performance. We should run this against some realistic test data and review the execution plan. We may consider adding indexes or HAVING a suitable normalised data model.
Limitations of Normalisation
This example only covers case changes and standard spaces. It does not cover full Unicode normalisation, specific email rules by provider or complex identity matching.
- What Have We learnt?
We have demonstrated some key concepts.
Don't rely on exact matches for your data quality checks. Small formatting changes can impact matching.
Aggregating data using SQL can help us turn individual records into meaningful data quality metrics.
Negative and positive testing can be useful. We can run our query against clean data to see what happens if there are no issues. We can also run our query against intentionally duplicated data.
Reporting suspected issues as a REVIEW can be more appropriate.
This example shows us how we can create a useful aggregated diagnostic within our database without exposing any email addresses.
- Further Improvements
We could improve our lab by:
Testing extra white space and internationalised email addresses.
Testing case insensitive matching against application specific unique rules.
Considering the performance of our query if we have a large amount of data. We could investigate the correct indexing strategy.
Implementing approved data quality controls within a scheduled monitoring process. Ensuring we have the correct authorisations, logging and reviewing processes.
- Source Code & Evidence
Our full SQL Diagnostics and Data Quality Toolkit can be found on GitHub:
Oracle AI Database 26ai – SQL Diagnostics and Data Quality Toolkit
This contains our duplicate email audit query, synthetic positive test, screenshots, CSV results and documentation.
Our duplicate email control can be found under DQ-005.
It was created as an additional control and does not effect the original 12 control status scorecard from our project.
Conclusion
Identifying potential duplicates is an important part of ensuring we have reliable data within our application.
Our lab shows how we can use Oracle SQL functions such as TRIM(), UPPER(), GROUP BY, HAVING and CASE to identify matching values and summarise our data quality findings.
We had no duplicate groups from our public HR example and 2 groups affecting 4 records from our synthetic positive test.
Overall we have learnt that we should have clear normalisation rules, correct testing, privacy focused reporting and validation against business rules when identifying duplicates.
The techniques shown in this example will be useful examples for any Oracle SQL Developers or DBA's wanting to add transparent read only data quality checks.
References
- Oracle Database SQL Language Reference – UPPER
https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/UPPER.html
- Oracle Database SQL Language Reference – TRIM
https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/TRIM.html
- Oracle Database SQL Language Reference – SELECT, GROUP BY and HAVING
https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/SELECT.html
- Oracle FreeSQL
- Source code & evidence
https://github.com/abdulrawof-totakhil/oracle-26ai-sql-diagnostics-toolkit
AI Assisted Technical Reference. Results from lab have been taken from contributors recorded Oracle FreeSQL evidence. This is not an Oracle ACE submission.
Top comments (0)