Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2009

    Unanswered: A Partial Except query?


    My sample data looks like this:


    ID Name Age

    2 Ted 34
    4 Kim 43
    5 Joe 29


    Name Age

    Ted 34
    Tom 23

    Except clause only works for exact column numbers and type matches. The result I'm looking for is this:

    4 Kim 43
    5 Joe 29

    Basically the minus of table 2 from table 1 for matching column name and age, but since table 2 is without an ID column, its getting tricky and I'm unable to use EXCEPT as I want the added ID information in the end result.

    The only way I could think of was:

    select,,tb1.age from
    (select name,age from table1
    select name,age from table2)
    inner join table1 on and table1.age=tb1.age

    Now if my simple table1 gets replaced by a select statement covering 5-6 joins, my solution above has got to be the least ideal way to do this isn't it?


  2. #2
    Join Date
    Jan 2007
    Jena, Germany
    Another option to avoid the join could be this:
    SELECT *
    FROM   table1
    WHERE  ( name, age ) NOT IN ( SELECT name, age
                                  FROM   table2 )
    The subselect should usually only be executed once because it is uncorrelated. (Depends on the optimizer decision, of course.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts