Discoveries in Familiar Languages #1

I love it when I'm working in a familiar programming language and find something new, particularly if that something new gives me more power or simplifies a pattern I've been using for a while. Such a discovery happened today.

Many-to-many relationship maintenance

I am constantly setting up and maintaining many-to-many relationships. I often set up the junction table to prevent duplication of relationships by adding a unique index on the appropriate key columns. Here's an example of such a junction table in MS SQL Server.

 CREATE TABLE [dbo].[ContactsGroups](  
  [keyContactGroup] [int] IDENTITY(1,1) NOT NULL,  
  [keyContact] [int] NOT NULL,  
  [keyGroup] [int] NOT NULL,  
  CONSTRAINT [PK_ContactsGroups] PRIMARY KEY CLUSTERED ([keyContactGroup] ASC) ON [PRIMARY]  
 )  

This is a pretty typical, very simple junction table. When I need to maintain this many-to-many relationship, I've typically taken a two-step process: DELETE and INSERT. Here is an example of those two queries as they would appear in ColdFusion.

 <cfquery datasource="..." result="qry_updateContact_groups">  
 DELETE FROM [ContactsGroups]  
 WHERE ([keyContact] = <cfqueryparam cfsqltype="cf_sql_integer" value="#Form.keyContact#" />)  
 <cfif ListLen(Form.keysGroups) GT 0>  
  AND ([keyGroup] NOT IN (<cfqueryparam cfsqltype="cf_sql_integer" value="#Form.keysGroups#" list="true" />))  
 </cfif>  
 </cfquery>  
 <cfif ListLen(Form.keysGroups) GT 0>  
  <cftry>  
   <cfquery datasource="..." result="qry_updateContact_groups">  
   INSERT INTO [ContactsGroups] ([keyContact], [keyGroup])  
   SELECT <cfqueryparam cfsqltype="cf_sql_integer" value="#Form.keyContact#" />, [Groups].[keyGroup]  
   FROM [Groups]  
   LEFT JOIN (  
    SELECT [keyGroup]  
    FROM [ContactsGroups]  
    WHERE ([keyContact] = <cfqueryparam cfsqltype="cf_sql_integer" value="#Form.keyContact#" />)  
   ) [ContactsGroups]  
    ON ([ContactsGroups].[keyGroup] = [Groups].[keyGroup])  
   WHERE (  
    [Groups].[keyGroup] IN (  
     <cfqueryparam cfsqltype="cf_sql_integer" value="#Form.keysGroups#" list="true" />  
    )  
   ) AND (COALESCE([ContactsGroups].[keyGroup], 0) = 0)  
   </cfquery>  
   <cfcatch><!--- An exception is thrown when nothing is inserted. ---></cfcatch>  
  </cftry>  
 </cfif>  

As you can see, this involved performing a LEFT JOIN to exclude items that already exist in the table. Today, while looking up a related issue (see the comments in the <cfcatch> block), I found a much easier way to write the same INSERT query.

  <cftry>  
   <cfquery datasource="#Request.ds.site#" result="qry_updateContact_groups">  
   INSERT INTO [ContactsGroups] ([keyContact], [keyGroup])  
   SELECT <cfqueryparam cfsqltype="cf_sql_integer" value="#Form.keyContact#" />, [keyGroup]  
   FROM [Groups]  
   WHERE ([keyGroup] IN (<cfqueryparam cfsqltype="cf_sql_integer" value="#Form.keysGroups#" list="true" />))  
   EXCEPT  
   SELECT [keyContact], [keyGroup]  
   FROM [ContactsGroups]  
   WHERE ([keyContact] = <cfqueryparam cfsqltype="cf_sql_integer" value="#Form.keyContact#" />)  
   </cfquery>  
   <cfcatch><!--- An exception is thrown when nothing is inserted. ---></cfcatch>  
  </cftry>  

Note the use of the EXCEPT keyword. This operates very similarly to the UNION keyword, but performs practically the opposite operation. Instead of creating a result set with the results of the first query followed by the results of the second query, it creates a result set with the results of the first query excluding results matching those of the second query. It performs the same task I was doing before, but it's more concise and (bonus!) more readable.