How Can a LEFT JOIN in SQL Identify Data Existing in One Table But Not Another?

Finding Non-Existing Data Using LEFT JOIN

In SQL, the need may arise to compare two tables and identify data that exists in one but not the other. This scenario is commonly encountered when checking for missing records.

Consider the following tables:

TABLE1

id	name	address
1	mm	123
2	nn	143

TABLE2

name	age
mm	6
oo	9

The objective is to find the non-existing names in TABLE1 by comparing it with TABLE2. In this case, the second row in TABLE1 with the name "nn" does not exist in TABLE2.

An initial attempt using an INNER JOIN:

SELECT w.*
FROM TABLE1 w
INNER JOIN TABLE2 v
ON w.name  v.name

will fail to retrieve the desired result because INNER JOIN only returns rows where there is a match in both tables.

The solution lies in using a LEFT JOIN instead:

SELECT w.*
FROM TABLE1 w
LEFT JOIN TABLE2 v ON w.name = v.name
WHERE ISNULL(v.name);

The LEFT JOIN performs an outer join, which means it will return all rows from the left table (TABLE1) regardless of whether there is a matching row in the right table (TABLE2). The ISNULL(v.name) condition checks for rows in TABLE1 where the corresponding name field in TABLE2 is null, indicating that the name does not exist in TABLE2.

The output will be:

id	name	address
2	nn	143

This query successfully retrieves the row from TABLE1 with the non-existing name "nn".

The above is the detailed content of How Can a LEFT JOIN in SQL Identify Data Existing in One Table But Not Another?. For more information, please follow other related articles on the PHP Chinese website!