Using "Select 1 from" to Check Existence

The SELECT 1 in a NOT EXISTS or EXISTS clause is a common pattern used in SQL when you just want to check for the existence of rows that match certain conditions, without actually needing to return any data.

Use Case

This is a shorthanded way to say "check if rows exists, but don't return any data". The 1 serves as a placeholder and avoids the retrieval of columns.

Why use `SELECT 1`?

Efficiency: It checks for row existence without selecting actual columns.
Performance: Using SELECT 1 or SELECT NULL minimizes data retrieval, making it an efficient existence check.
Simplicity: The 1 convention keeps queries focused on existence checking rather than data retrieval.

Example:

SELECT
    id, 
    name,
    salary
FROM source_table s
WHERE NOT EXISTS (
    SELECT 1
    FROM target_table t
    WHERE t.id = 123  -- Checks if the id=123 already exists in the target_table
);

The NOT EXISTS clause checks if the subquery returns any rows.
If the subquery returns any rows that match the condition, NOT EXISTS returns FALSE. This means the condition isn't met; ensures that the query only returns rows where no matching rows exist in the target_table based on a condition.
If the subquery returns no rows, if the subquery finds any row in the target_table with id = 123. This will return true, causing the outer query to exclude that row from the results.