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

How to find records in one table that do not exist in another table?

In a relational database, you may need to find records in one table that do not exist in another table. A common example is identifying call records for individuals whose phone numbers are not registered in the phone book.

Consider the following scenario:

<code>电话簿 (Phone_book)
+----+------+--------------+
| id | name | phone_number |
+----+------+--------------+
| 1  | John | 111111111111 |
+----+------+--------------+
| 2  | Jane | 222222222222 |
+----+------+--------------+

来电记录 (Call)
+----+------+--------------+
| id | date | phone_number |
+----+------+--------------+
| 1  | 0945 | 111111111111 |
+----+------+--------------+
| 2  | 0950 | 222222222222 |
+----+------+--------------+
| 3  | 1045 | 333333333333 |
+----+------+--------------+</code>

To find call records for an individual whose phone number is not in the phone book, you can use several methods:

Method 1: Use 'NOT IN' subquery

<code class="language-sql">SELECT *
FROM Call
WHERE phone_number NOT IN (SELECT phone_number FROM Phone_book)</code>

This query directly checks whether the phone number in the 'Call' table appears in the 'Phone_book' table. If not, select the corresponding call record.

Method 2: Use 'NOT EXISTS' subquery

<code class="language-sql">SELECT *
FROM Call
WHERE NOT EXISTS
(SELECT *
FROM Phone_book
WHERE Phone_book.phone_number = Call.phone_number)</code>

Similar to the 'NOT IN' subquery, this method uses the 'NOT EXISTS' subquery to check if a matching phone number exists in the 'Phone_book' table.

Method 3: Use 'LEFT OUTER JOIN'

<code class="language-sql">SELECT * 
FROM Call
LEFT OUTER JOIN Phone_Book
ON (Call.phone_number = Phone_book.phone_number)
WHERE Phone_book.phone_number IS NULL</code>

This method uses 'LEFT OUTER JOIN' to retrieve all incoming call records and then filters out records with matching phone numbers in the 'Phone_book' table by checking for NULL values ​​in the 'Phone_book.phone_number' column.

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