How to Identify Unregistered Callers Using SQL Queries?

Identifying Unknown Callers: SQL Techniques for Unregistered Numbers

This article demonstrates how to identify phone calls from numbers not listed in a phone book database using SQL queries. We'll explore several efficient methods to achieve this.

Scenario:

Consider these tables:

Table: Phone_book

id	name	phone_number
1	John	111111111111
2	Jane	222222222222

Table: Call

id	date	phone_number
1	0945	111111111111
2	0950	222222222222
3	1045	333333333333

The objective is to find calls from numbers not in the Phone_book.

SQL Query Solutions:

Three methods are presented below:

Method 1: NOT IN Subquery

This approach uses a NOT IN subquery to efficiently filter out calls with numbers present in the Phone_book:

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

Method 2: NOT EXISTS Subquery

This alternative uses NOT EXISTS to achieve the same result. It's often considered more efficient for large datasets:

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

Method 3: LEFT OUTER JOIN with NULL Check

This method employs a LEFT OUTER JOIN and filters for NULL values in the Phone_book column to identify calls without matching entries:

<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>

Expected Result:

All three queries will produce the same output:

Table: Call

id	date	phone_number
3	1045	333333333333

This correctly identifies the call from the unregistered number. Choose the method that best suits your database system and data size.

The above is the detailed content of How to Identify Unregistered Callers Using SQL Queries?. For more information, please follow other related articles on the PHP Chinese website!