I love it when a plan comes together

I’m currently working on an application so that our Marketing department can produce most of their own mailing lists without my having to get involved. It was all going well until I got stuck on the bit where the actual SQL query is generated but a rummage in Books Online revealed a very clean solution using some constructs that I had previously dismissed as pointless.
Typically we want to email every customer who is in any of the following n groups. Experience shows that a group has the following definition:
<people who have done A>
[(AND <people who have done B>) | (OR <people who have done C>)]
[APART FROM <people who have done D>]
When doing these by hand I’ve been using INNER JOIN for the AND, UNION for the OR, and LEFT JOIN + WHERE D IS NULL for the APART FROM. This would produce two quite different queries:
-- Old OR
select  A.PersonID
from  (
  -- A
  select  PersonID
  from  ...
  union  -- OR
  -- C
  select  PersonID
  from  ...
  ) AorC
  left join  -- APART FROM
  (
  select  PersonID
  from  ...
  ) D on D.PersonID = AorC.PersonID
where  D.PersonID is null 

-- Old AND
select  distinct main.PersonID
from  (
  -- A
  select  PersonID
  from  ...
  ) A
  inner join  -- AND
  (
  -- B
  select  PersonID
  from  ...
  ) B on B.PersonID = A.PersonID
  left join  -- APART FROM
  (
  select  PersonID
  from  ...
  ) D on D.PersonID = A.PersonID
where  D.PersonID is null

 

But when I tried to write the code that can generate the SQL for any combination of those (along with all the variables controlling what each SELECT did and what was in all the optional bits of each WHERE clause) my brain started to hurt. Then I remembered reading about the (then new to me) keywords INTERSECT and EXCEPT. At the time I couldn’t see what they added but I thought I would have a play and see if they might help. They were perfect for this. Here’s the new query structure:

-- The way forward
select  PersonID
from  (
    (
      (
      -- A
      select  PersonID
      from  ...
      )
      union      -- OR
      intersect  -- AND
      (
      -- B/C
      select  PersonID
      from  ...
      )
    )
    except
    (
    -- D
    select  PersonID
    from  ...
    )
  ) x

I can easily swap between between UNION and INTERSECT, and omit B, C, or D as necessary. Elegant, clean and readable – pick any 3! Sometimes it really pays to read the manual.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s