Junior — Middle

How can you identify users from the first table who do not have a related record in the second table using SQL?

sobes.tech AI

Answer from AI

To identify users from the first table who do not have a related record in the second table, several approaches in SQL can be used:

LEFT JOIN with a NULL check in the second table:

SELECT t1.*
FROM first_table t1
LEFT JOIN second_table t2 ON t1.id = t2.user_id
WHERE t2.user_id IS NULL;

Here, we perform a left join and select records from the first table for which there are no corresponding records in the second (i.e., the fields of the second table will be NULL).

Using NOT EXISTS:

SELECT *
FROM first_table t1
WHERE NOT EXISTS (
    SELECT 1 FROM second_table t2 WHERE t2.user_id = t1.id
);

Using NOT IN:

SELECT *
FROM first_table
WHERE id NOT IN (SELECT user_id FROM second_table WHERE user_id IS NOT NULL);

The first and second options are generally preferred in terms of performance and correctness when NULL values are present in the data.

Previous Next