How to Identify Calls from Unlisted Phone Numbers in a Linked Database?

Finding Call Records from Unknown Numbers

Our database includes two tables: 'Phone_book' (containing names and phone numbers) and 'Call' (containing call logs). The goal is to pinpoint calls originating from numbers not listed in the 'Phone_book' table.

Solutions:

Several SQL queries can accomplish this; efficiency depends on database optimization and table sizes.

Method 1 (NOT IN):

This straightforward query is efficient for smaller 'Phone_book' tables:

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

Method 2 (NOT EXISTS):

An alternative approach, often preferred for performance with larger datasets:

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

Method 3 (LEFT OUTER JOIN):

A third option using a 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>

Important Note: For optimal performance, replace SELECT * with a list of specific columns needed from the 'Call' table. This reduces data retrieval and improves query speed.

The above is the detailed content of How to Identify Calls from Unlisted Phone Numbers in a Linked Database?. For more information, please follow other related articles on the PHP Chinese website!