How to Select Records from One Table That Don't Exist in Another?

Identifying Missing Records Across Database Tables

Database queries frequently require retrieving data from one table that lacks a corresponding entry in another. Let's illustrate this with an example:

Scenario:

We have two tables, 'table1' and 'table2', with the following structures:

<code>table1 (id, name)
table2 (id, name)</code>

Objective:

Our goal is to extract the 'name' values from 'table2' that are absent in 'table1'.

A naive approach might be:

<code>SELECT name
FROM table2
-- where name is not in table1</code>

Unfortunately, this SQL query is incomplete and won't yield the expected outcome.

Effective Solution:

The correct approach involves utilizing a LEFT JOIN:

<code class="language-sql">SELECT t2.name
FROM table2 t2
LEFT JOIN table1 t1 ON t1.name = t2.name
WHERE t1.name IS NULL;</code>

Explanation:

• LEFT JOIN: This ensures that all rows from 'table2' are included in the result set, regardless of matching rows in 'table1'.
• WHERE Clause: The condition WHERE t1.name IS NULL filters the results, retaining only those rows from 'table2' where there's no corresponding 'name' in 'table1'.
• Field Selection: The query selects the 'name' column from 'table2', as this is the only field guaranteed to exist from that table in the final result.

This LEFT JOIN technique is widely compatible across various database systems, offering a robust method for identifying missing records between tables.

The above is the detailed content of How to Select Records from One Table That Don't Exist in Another?. For more information, please follow other related articles on the PHP Chinese website!