Performing set subtraction on a single table

Question

I have a table that stores membership records. One record per member, per year. eg:

MEMBER   YEAR
--------------
steve    2011
steve    2010
bob      2010
jane     2011

What I'm trying to generate is a list of lapsed members (those who haven't renewed) for a given year. Something like "get all members who have an entry for 2010 but not 2011".

I've been wrestling with this one for a while and the closest examples I can find involve comparing two tables.

Answer 1

Using LEFT JOIN/IS NULL

   SELECT a.member
     FROM YOUR_TABLE a
LEFT JOIN YOUR_TABLE b ON b.member = a.member
                      AND b.year = 2011
    WHERE a.year = 2010
      AND b.member IS NULL

Using NOT EXISTS

   SELECT a.member
     FROM YOUR_TABLE a
    WHERE a.year = 2010
      AND NOT EXISTS (SELECT NULL
                        FROM YOUR_TABLE b 
                       WHERE b.member = a.member
                         AND b.year = 2011)

Using NOT IN

SELECT a.member
  FROM YOUR_TABLE a
 WHERE a.year = 2010
   AND a.member NOT IN (SELECT b.member
                          FROM YOUR_TABLE b 
                         WHERE b.year = 2011)

Performance

Performance depends on if the columns compared are NULLable (the values can be NULL):

• if they are NULLable, NOT EXISTS is the best choice.
• If they are not nullable, in MySQL the LEFT JOIN/IS NULL is the best choice.