Single SQL query to find rows where a value is present for one key but not another given key

Question

Hopefully the title is clear enough! I'm looking for a single query that replicates the functionality of the following one, but without using a subquery:

select p_id from a_p 
 where a_id=1 
   and p_id not in (select p_id from a_p where a_id=2)

For example, table a_p has the following rows:

a_id | p_id
1    | 1
1    | 2
2    | 2

Here, p_id 1 is present for a_id 1 but not a_id 2 - the query above will return only p_id 1. Any ideas?

Answer 1

Without being able to use a subquery, that leaves you with using LEFT OUTER JOIN/IS NULL:

   SELECT a.p_id
     FROM A_P a
LEFT JOIN A_P b ON b.p_id = a.p_id
               AND b.a_id = 2
    WHERE a.a_id = 1
      AND b.p_id IS NULL

Be aware that subquery performance is not the same across all database vendors. In PostgreSQL and Oracle, NOT IN, NOT EXISTS and LEFT JOIN/IS NULL are all equivalent. Only in MySQL is the LEFT JOIN/IS NULL more efficient.