How do I find orphan rows, duplicates and missing values?

Applies to BroadSQL 5.5.0 · reviewed October 18, 2026

What you'll accomplish

Before a migration, before adding a constraint, or when a report looks wrong, you need to know what is wrong in the data itself: rows that point to a parent that does not exist, values that appear twice where they should be unique, and empty fields that should be filled.

You will run the three classic checks on WORLD, read what each one finds, then save them as one Script that you can run on any environment and export to a worksheet. The checks find real problems in the sample: 30 cities whose country does not exist, a city listed twice in the same district, and countries without a capital. Every check of this page only reads.

Short answer

Orphans: the child LEFT JOIN its parent, WHERE <parent key> IS NULL. Duplicates: GROUP BY the columns that should be unique, HAVING COUNT(*) > 1. Missing values: COUNT(*) next to COUNT(<column>). Save the checks in a Script and run it with DUMP LIB ... AS XLSX for a report.

Before you start

Connect to the sample with CONNECT WORLD;. Running the checks on several environments (section 6) needs the practice connections of Set up QA and production copies of WORLD, which do not exist by default.

1. Orphans: rows whose parent does not exist

CITY.COUNTRYCODE holds the code of a country, but WORLD declares no foreign key between CITY and COUNTRY, so nothing stops a city from pointing to a country that is not there (see Check what depends on a table before you change it). To find such rows, join each city to its country and keep the cities for which no country was found:

SELECT C.COUNTRYCODE, COUNT(*) AS CITIES
  FROM CITY C LEFT JOIN COUNTRY K ON K.CODE = C.COUNTRYCODE
 WHERE K.CODE IS NULL
 GROUP BY C.COUNTRYCODE ORDER BY C.COUNTRYCODE;

Two country codes of CITY do not exist in COUNTRY:

WORLD> SELECT C.COUNTRYCODE, COUNT(*) AS CITIES FROM CITY C LEFT JOIN COUNTRY K ON K.CODE = C.COUNTRYCODE WHERE K.CODE IS NULL GROUP BY C.COUNTRYCODE ORDER BY C.COUNTRYCODE;
|-----------|--------------------|
|COUNTRYCODE|CITIES              |
|-----------|--------------------|
|ROM        |29                  |
|TMP        |1                   |
|-----------|--------------------|

2 rows fetched in 6 ms.

Look at the rows behind the codes to understand them:

SELECT ID, TRIM(NAME) AS NAME, COUNTRYCODE, POPULATION FROM CITY WHERE COUNTRYCODE IN ('ROM', 'TMP') ORDER BY POPULATION DESC;

The list starts with Bucuresti (ID 3018), Iasi and Constanta, and ends with Dili (ID 1522). COUNTRY holds Romania under ROU and East Timor under TLS, and their capitals are exactly 3018 and 1522: the countries were given their current codes, and the cities kept the old ones. Run the same check on COUNTRYLANGUAGE, with L for the language table, and you find 6 rows under ROM and 2 under TMP.

Check the other direction too. COUNTRY.CAPITAL points to a city; this count returns 0, so every capital exists:

SELECT COUNT(*) AS MISSING_CAPITALS FROM COUNTRY K LEFT JOIN CITY C ON C.ID = K.CAPITAL WHERE K.CAPITAL IS NOT NULL AND C.ID IS NULL;

2. Duplicates: values that would break a unique constraint

Before adding a unique constraint, say on the name of a city within its country, find the values that would make it fail. TRIM matters on WORLD's fixed-length NAME column, so that two names compare on their letters only:

SELECT COUNTRYCODE, TRIM(NAME) AS NAME, COUNT(*) AS OCCURRENCES
  FROM CITY GROUP BY COUNTRYCODE, TRIM(NAME) HAVING COUNT(*) > 1
 ORDER BY OCCURRENCES DESC, COUNTRYCODE, NAME;

The check returns 22 pairs. The first rows:

WORLD> SELECT COUNTRYCODE, TRIM(NAME) AS NAME, COUNT(*) AS OCCURRENCES FROM CITY GROUP BY COUNTRYCODE, TRIM(NAME) HAVING COUNT(*) > 1 ORDER BY OCCURRENCES DESC, COUNTRYCODE, NAME;
|-----------|-----------------------------------|--------------------|
|COUNTRYCODE|NAME                               |OCCURRENCES         |
|-----------|-----------------------------------|--------------------|
|USA        |Springfield                        |3                   |
|CHN        |Jining                             |2                   |
|CHN        |Jinzhou                            |2                   |
|CHN        |Kaiyuan                            |2                   |
|CHN        |Suzhou                             |2                   |
...

A duplicate check finds candidates; the business decides which are errors. The three Springfields are in Massachusetts, Missouri and Illinois: three real cities. The real key of a city includes its district, so check that instead:

SELECT COUNTRYCODE, TRIM(NAME) AS NAME, TRIM(DISTRICT) AS DISTRICT, COUNT(*) AS OCCURRENCES
  FROM CITY GROUP BY COUNTRYCODE, TRIM(NAME), TRIM(DISTRICT) HAVING COUNT(*) > 1;

One pair remains: Jinzhou, in the Liaoning district of China, listed twice (IDs 1944 and 2238, with different populations). That one is worth a question to whoever owns the data.

3. Missing values: empty fields that should be filled

COUNT(*) counts rows, COUNT(<column>) counts the rows where the column has a value. Side by side, they show how many rows lack each value:

SELECT COUNT(*) AS COUNTRIES, COUNT(CAPITAL) AS WITH_CAPITAL, COUNT(INDEPYEAR) AS WITH_INDEPYEAR,
       COUNT(LIFEEXPECTANCY) AS WITH_LIFEEXPECTANCY FROM COUNTRY;

Of 254 countries, 7 have no capital, 43 no independence year and 16 no life expectancy:

WORLD> SELECT COUNT(*) AS COUNTRIES, COUNT(CAPITAL) AS WITH_CAPITAL, COUNT(INDEPYEAR) AS WITH_INDEPYEAR, COUNT(LIFEEXPECTANCY) AS WITH_LIFEEXPECTANCY FROM COUNTRY;
|--------------------|--------------------|--------------------|--------------------|
|COUNTRIES           |WITH_CAPITAL        |WITH_INDEPYEAR      |WITH_LIFEEXPECTANCY |
|--------------------|--------------------|--------------------|--------------------|
|254                 |247                 |211                 |238                 |
|--------------------|--------------------|--------------------|--------------------|

1 rows fetched in 4 ms.

A missing value is a finding only where the business says the field is required. SELECT CODE, TRIM(NAME) AS NAME FROM COUNTRY WHERE CAPITAL IS NULL; lists Antarctica, Bouvet Island and five other uninhabited territories: no capital is expected there. Narrow the independence year to populated countries to see the cases worth a question:

SELECT CODE, TRIM(NAME) AS NAME, POPULATION FROM COUNTRY WHERE INDEPYEAR IS NULL AND POPULATION > 1000000 ORDER BY POPULATION DESC;

It lists Hong Kong, Puerto Rico and Palestine: a matter of definition, not an error in the data.

4. Save the checks as one Script

A check you run once is an answer; a check you can run again is a control. Save the checks in the Scripts Library (the scripts folder), with one final query that sums up each finding on one row, so that the Script has a single result to export:

-- @description: Data quality checks of the WORLD sample: orphans, duplicates, missing values
-- @tags: world, quality
SELECT 'Cities whose country does not exist' AS CHECK_NAME, COUNT(*) AS ROW_COUNT
  FROM CITY C LEFT JOIN COUNTRY K ON K.CODE = C.COUNTRYCODE WHERE K.CODE IS NULL
UNION ALL
SELECT 'Languages whose country does not exist', COUNT(*)
  FROM COUNTRYLANGUAGE L LEFT JOIN COUNTRY K ON K.CODE = L.COUNTRYCODE WHERE K.CODE IS NULL
UNION ALL
SELECT 'Cities listed twice in one district', COUNT(*)
  FROM (SELECT COUNTRYCODE FROM CITY GROUP BY COUNTRYCODE, TRIM(NAME), TRIM(DISTRICT) HAVING COUNT(*) > 1) D
UNION ALL
SELECT 'Countries without a capital', COUNT(*) - COUNT(CAPITAL) FROM COUNTRY;

Run it on WORLD:

@quality/world-quality.bsql;

The result has one row per check, and the status line that follows says SUCCESS:

|--------------------------------------|--------------------|
|CHECK_NAME                            |ROW_COUNT           |
|--------------------------------------|--------------------|
|Cities whose country does not exist   |30                  |
|Languages whose country does not exist|8                   |
|Cities listed twice in one district   |1                   |
|Countries without a capital           |7                   |
|--------------------------------------|--------------------|

4 rows fetched in 12 ms.

5. Export a report

DUMP LIB runs the Script and writes its final result to a file, only when the run ends with the status SUCCESS, so a report never comes from a run that failed halfway:

DUMP LIB quality/world-quality.bsql TO quality_report.WORLD AS XLSX;

The worksheet WORLD of quality_report.xlsx, in the export folder, holds the four counts, and its QUERIES worksheet records the source and the date.

6. The same checks on every environment

With the practice set, WORLD is the production connection of the Database Group WORLDAPP, and the same Script checks QA. ENV QA opens the QA connection of that group, and the report gets a worksheet of its own:

ENV QA;
DUMP LIB quality/world-quality.bsql TO quality_report.QA AS XLSX;

The checks only read, so they run on a Production connection without any confirmation.

Variations

• Your own tables: keep the three patterns and change the tables and columns. A check is only as good as the relationship it assumes: Check what depends on a table helps you find the undeclared ones.
• A weekly history: add CURRENT_DATE AS CHECK_DATE to the final query and export with MODE APPEND, to see whether the counts go down.
• A ready-made duplicate check: the Scripts Gallery offers a template to adapt.
• On a large table: count first with CNT <table>;, then run the detail queries on a filtered subset.

Watch out

• LEFT JOIN ... WHERE <parent key> IS NULL needs the parent key in the WHERE, not a column that can itself be NULL.
• GROUP BY on a CHAR column compares padded values: trim it, as above.
• A finding is a question to the data owner, not a correction. To fix the orphan cities safely, see Verify that a data fix changed only the intended rows.

Clean up

Delete quality_report.xlsx from the export folder. The Script can stay in the library.

Related

• Explore an unfamiliar database
• Save a query with parameters and run it by name
• Write a report Script that stops at the first failure
• Exporting Script results

Commands used

• CNT
• @
• DUMP

Found something wrong or unclear on this page? Tell us, with the page address.