---
title: "Help with Writing a Query to Find Orphaned Records in SQL Server"  
description: "Help with Writing a Query to Find Orphaned Records in SQL Server"  
author: "Anubhav Sharma"  
published: 2024-07-15  
updated: 2024-07-16  
canonical: https://www.mindstick.com/forum/160915/help-with-writing-a-query-to-find-orphaned-records-in-sql-server  
category: "SQL Server"  
tags: ["database", "sql server", "sql server 2008", "sql server 2012", "sql server 2022"]  
reading_time: 3 minutes  

---

# Help with Writing a Query to Find Orphaned Records in SQL Server

Hi,

I need a query to find orphaned records in the `Orders` [table](https://www.mindstick.com/articles/43918/how-to-design-table-using-bootstrap) that do not have a matching `CustomerID` in the `Customers` table. Can someone assist me?

## Replies

### Reply by Ravi Vishwakarma

## Orphaned Records

An orphaned record is a record whose [foreign key](https://www.mindstick.com/forum/159679/difference-between-primary-kay-foreign-key-and-composite-key) value references a non-existent [primary key](https://www.mindstick.com/forum/159679/difference-between-primary-kay-foreign-key-and-composite-key) value.

Orphaned records are a concept within database relationships. If a row in a related table references a non-existent row in the primary table, it is said to be an orphaned row. This is because it has no “parent” with which its data is associated with. The terms orphaned row and orphaned record tend to be used interchangeably, even though there’s a subtle difference between a row and a record.

If we delete record number 15 in a primary table, but there’s still a related table with the value of 15, we end up with an orphaned row.

![Screenshot of a diagram depicting an orphaned record.](https://database.guide/wp-content/uploads/2016/05/referential-integrity-orphaned-record.png)

Here, the related table contains a foreign key value that doesn’t exist in the primary key field of the primary table. This has resulted in an **“orphaned record”.**

## Example

To find orphaned records in the `Orders` table that do not have a matching `CustomerID` in the `Customers` table, you can use a `LEFT JOIN` combined with a `WHERE` clause to identify the rows where the join did not find a match.

Here’s an example query:

```plaintext
SELECT
    Orders.OrderID,
    Orders.CustomerID,
    Orders.OrderDate
FROM
    Orders
LEFT JOIN
    Customers ON Orders.CustomerID = Customers.CustomerID
WHERE
    Customers.CustomerID IS NULL;

```

### Explanation:

- `SELECT Orders.OrderID, Orders.CustomerID, Orders.OrderDate`: Selects the columns from the `Orders` table that you want to display.
- `FROM Orders`: Specifies the `Orders` table as the main table to query.
- `LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID`: Performs a `LEFT JOIN` between the `Orders` and `Customers` tables on the `CustomerID` column.

   - A `LEFT JOIN` includes all records from the `Orders` table and the matched records from the `Customers` table. If no match is found, the result is `NULL` from the `Customers` table.

- `WHERE Customers.CustomerID IS NULL`: Filters the results to include only those rows where there is no matching `CustomerID` in the `Customers` table. This identifies the orphaned records in the `Orders` table.

This query will return the `OrderID`, `CustomerID`, and `OrderDate` of all orders that do not have a corresponding customer in the `Customers` table.

## Read more

[**How to Join Multiple Tables and Retrieve Specific Columns in SQL Server?**](https://www.mindstick.com/forum/160909/how-to-join-multiple-tables-and-retrieve-specific-columns-in-sql-server)

[**How to Write a Query to Get Records with a Specific Date Format in SQL Server?**](https://www.mindstick.com/forum/160920/how-to-write-a-query-to-get-records-with-a-specific-date-format-in-sql-server)

[**SQL Query to Calculate Age from Date of Birth in SQL Server**](https://www.mindstick.com/forum/160919/sql-query-to-calculate-age-from-date-of-birth-in-sql-server)

[**Help with Writing a Subquery to Get Aggregate Data in SQL Server**](https://www.mindstick.com/forum/160910/help-with-writing-a-subquery-to-get-aggregate-data-in-sql-server)


---

Original Source: https://www.mindstick.com/forum/160915/help-with-writing-a-query-to-find-orphaned-records-in-sql-server

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.