Performing set subtraction on a single table

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开发者_StackOverflow tables.

---

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.

---

If we will simplify this problem, it will be, "Get the list of members who have not renewed for year x"

If we split this problem, it will basically yield, get all the members whose registration is less than specified year (x).

so we can write the query as

select Member from membership where year < 2011

You can use a parameter of some means to build this query so that you can specify a year.

Thanks

Neelesh