Daenmark

« My favourite lightbulb joke | Main | Dragsholm Slot »

October 26, 2006

SQL for checking whether a set already exists

The following SQL will give all other lists which exactly match the specified list. Works because, in the case of equality, the union and intersection of two sets is the same, so the difference between them (the 'MINUS' operator in Oracle) returns the null (empty) set, which is tested for by 'NOT EXISTS'. Nifty, eh?

SELECT   lt.list_id
FROM     list_table lt  
GROUP BY lt.list_id 
HAVING NOT EXISTS 
 (
  (SELECT list_id 
  FROM   list_table 
  WHERE  list_id = :list_id 
    UNION 
  SELECT list_id 
  FROM   list_table 
  WHERE  list_id = lt.list_id) 
      MINUS
  (SELECT list_id 
  FROM   list_table 
  WHERE  list_id = :list_id 
    INTERSECT 
  SELECT list_id 
  FROM   list_table 
  WHERE  list_id = lt.list_id) 
 )
AND 
  lt.list_id <> :list_id;

Posted by daen at October 26, 2006 02:14 AM

Comments

Post a comment