Geek asks and answers

Performing a 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 create is a list of expired members (those who have not been updated) for a given year. Something like "get all the participants who have an entry for 2010, but not for 2011."

I struggled with this for a while, and the closest examples I can find include comparing two tables.

+3
source share
2 answers

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 whether NULLable columns are compared (values ​​can be NULL):

+3

, : " , x"

, , , (x).

select Member from membership where year < 2011

, .

Neelesh

0

More articles:


All Articles