Скачать или смотреть How to Select Values from One SQL Table Not Present in Another

Discover how to accurately select IDs from one table that are not found in another using SQL. We’ll guide you through the process step-by-step!
---
This video is based on the question https://stackoverflow.com/q/67735269/ asked by the user 'LdM' ( https://stackoverflow.com/u/14684140/ ) and on the answer https://stackoverflow.com/a/67735379/ provided by the user 'sddk' ( https://stackoverflow.com/u/1123146/ ) 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: Values from a table that are not in another one

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

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Select Values from One SQL Table Not Present in Another: A Simple Guide

When working with databases, it’s common to encounter scenarios where you need to compare the values in two tables. Perhaps you have one table containing users with their ages and another table with schools. You may want to identify which IDs from the school table are not present in the age table. In this guide, we’ll explore how to achieve this with SQL, specifically focusing on the situation presented in the query below.

The Problem

Let's consider the two tables we have:

Table A: Contains IDs and ages of individuals.

Table B: Contains IDs and school names for some other set of individuals.

Here are the values in each table:

Table A:

IDAge1242254522Table B:

IDSchool34school11school2The question arises: How can you select the IDs that are in Table B but not in Table A? The initial query attempted to use a LEFT OUTER JOIN, but was returning the same count of rows as an INNER JOIN. Let's break down how to correct this situation.

The Solution

Using NOT IN Clause

When you want to select entities from one table that do not exist in another table, you can use the NOT IN clause in your SQL query. Here’s how it works:

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

How It Works

Subquery: The inner query (Select ID From A) retrieves all IDs present in Table A.

Parent Query: The outer query (Select * From B Where ID Not In (...)) returns all records from Table B where the ID is not found in the results of the inner query.

This approach efficiently filters out IDs that are present in Table A, ensuring you get only those in Table B that are not found in Table A.

Explaining the Original Approach

In the original query, the writer used a LEFT OUTER JOIN combined with an INNER JOIN. Here’s why it didn't work:

LEFT OUTER JOIN: This type of join retrieves all records from the left table (Table B) and matching records from the right table (Table A). If there’s no match, the result is still included, but with NULLs for the right side.

INNER JOIN: This type of join only retrieves records that have matching entries in both tables.

When these two joins were combined without appropriately filtering conditions, the result mirrored that of an INNER JOIN, losing the desired distinction.

Conclusion

In summary, selecting IDs from one table that do not exist in another can be easily achieved using the NOT IN clause in SQL. Make sure you clearly define your subqueries and the conditions in your main query to avoid any confusion arising from join operations. By applying these techniques, you can efficiently retrieve the data you need from your SQL tables.

For further assistance with SQL queries or troubleshooting, feel free to reach out or leave your comments below! Happy querying!