How to Fill in Missing Records in SQL Server with LEFT JOINs

Learn how to ensure complete result sets in SQL Server by filling in missing records using CROSS JOIN and LEFT JOIN techniques.
---
This video is based on the question https://stackoverflow.com/q/69802369/ asked by the user 'beardedwonder6' ( https://stackoverflow.com/u/4772039/ ) and on the answer https://stackoverflow.com/a/69802430/ provided by the user 'Dale K' ( https://stackoverflow.com/u/1127428/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: SQL Server Filling in Missing Record

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/licensing
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Introduction

When working with data in SQL Server, you may often encounter situations where certain records appear to be missing from your results. This often happens when joining multiple tables where some records in one table don’t correspond to entries in another. In this post, we will explain how to resolve this issue using a practical scenario involving a review system represented by two tables: # func and # review.

The Problem: Missing Records

In our scenario, we have two tables:

# func: This table contains the business functions that are responsible for reviewing items.

# review: This table captures the review status of an item by each function, noting whether the review is in progress or complete.

To illustrate, here are the entries in each table:

# func (Business Functions)

1: Marketing

2: Safety

3: Provisioning

4: Boss

# review (Review Status)

For Item 1: Marketing (Complete), Safety (Complete), Provisioning (Complete)

For Item 2: Marketing (Complete), Safety (Complete), Provisioning (Complete), Boss (Complete)

When joining these tables to retrieve review status alongside function names, we may notice that missing entries cause incomplete results. Specifically, the review status for the Boss function for itemId = 1 does not appear with the expected results.

The Solution: Expanding Result Sets with CROSS JOIN

To address this problem, we will create a complete dataset by generating all possible combinations of items and functions using a CROSS JOIN. This technique will allow us to ensure that every function is represented for each item, even if the review status is missing.

Step-by-Step Solution

Use a CROSS JOIN: Start by creating combinations of all items from the # review table with all functions from the # func table. This establishes a baseline for every function to be listed with each item.

Left Join: Next, perform a LEFT JOIN on the # review table to add the actual review statuses to the combinations created earlier. This way, if no review entry exists for a specific function and item, it will still display as missing.

Select Required Columns: Finally, select the necessary columns to retrieve the output in a user-friendly format.

Here’s the SQL code implementing this solution:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Code

CROSS JOIN: This creates a Cartesian product of the # func table with unique itemId values from the # review table.

LEFT JOIN: This joins the # review table back to the result set on the function IDs and item IDs, allowing us to fill in the review statuses. If a review doesn't exist, the COALESCE function sets the status to 0 (indicating a missing review).

COALESCE Function: This function is crucial in ensuring that instead of showing NULL for missing reviews, we display 0. This way, it's clear that the function was indeed not reviewed for that specific item.

Conclusion

This approach ensures a comprehensive result that clearly indicates any missing reviews. By utilizing CROSS JOIN alongside LEFT JOIN, we can effectively fill in gaps, leading to clearer insights from our data without losing integrity.

By implementing these techniques, you can solve similar issues in your SQL queries with confidence. Happy querying!

Видео How to Fill in Missing Records in SQL Server with LEFT JOINs канала vlogize