Forum Stats

  • 3,873,081 Users
  • 2,266,504 Discussions
  • 7,911,416 Comments

Discussions

Newbie: Simple select with 'IN ALL'

13

Comments

  • 569964
    569964 Member Posts: 278
    Volder
    but would give wrong result in case duplicate records exist in the table!
    hehe, yup! But I was trying to make it as small as possible.

    You're dead right though, distinct id_state should be used instead of *

    Also, I do wish Oracle had better set capabilities, etc.

    There are always some things (not just sets) that seem really easy to do... until you actually try to implement them in SQL...
  • 578885
    578885 Member Posts: 93
    Wouldn't it be nice if we had a set operator
    say 'INALL' which would do the job!!

    Actually I was looking for an answer like this as you can see from the subject line!
  • William Robertson
    William Robertson Member Posts: 9,568 Bronze Crown
    edited Aug 31, 2007 5:56AM
    > Also, I do wish Oracle had better set capabilities, etc.

    As I'm sure you know, relational division is a notoriously tricky challenge in SQL and many people have said that it highlights the weakness of SQL itself (although opinion is divided on whether it is a very common requirement or a rare and exotic one). I'd certainly be interested if Oracle added some new set operator to help with this but I doubt that it will ever be straightforward.

    What else is missing, out of interest?
  • This is rather innovative technique! However, the queries with explicit set operations are much more intuitive

    select person from t where id_state = 2
    intersect
    select person from t where id_state = 3
    intersect
    select person from t where id_state = 4

    which returns all the persons who's set of id_states contains {2,3,4}, and

    select person from t where id_state = 2
    intersect
    select person from t where id_state = 3
    intersect
    select person from t where id_state = 4
    minus
    select person from t where id_state not in (2,3,4)

    which returns all the persons who's set of id_states is exactly {2,3,4}.
  • Vadim Tropashko-Oracle
    Vadim Tropashko-Oracle Posts: 1,261 Employee
    edited Aug 31, 2007 6:28PM
    Pretending that we cast votes here, I would prefer extending the IN operator to handle subqueries for both operands, e.g.
    select distinct Name from ApplicantSkills i
    where (select Language from JobRequirements ii 
               where ii.Name = i.Name) 
       IN 
              (select Language from ApplicantSkills)
    where IN operator is interpreted as a "subset of"

    Without an explicit subset relation available we have to express it as emptiness of the difference between the two sets. Formally
    A ⊆ B
    is equivalent to
    A \ B = ∅
    Applied to our case it allows us to transform our rough first attempt to a legitimate SQL query
     
    select distinct Name from ApplicantSkills i
    where not exists ( 
        select Language from ApplicantSkills
        minus
        select Language from JobRequirements ii 
        where ii.Name = i.Name
    )
    So if the the IN or SUBSET OF operator would rewrite to the above "not exists" predicate, then the feature is minuscule.

    Message was edited by:
    Vadim Tropashko
  • William Robertson
    William Robertson Member Posts: 9,568 Bronze Crown
    That would make sense.

    Or, perhaps SUBSET OF would be easier to read (not that the inventors of MODEL and partition outer joins seem to care about syntax being easy to read).
  • 45736
    45736 Member Posts: 153
    Aketi's solution and COLLECT - SUBMULTISET seems to be two general solutions.

    However, between these two solutions which will bring more "strain" on the database? From previous discussion on COLLECT in this forum it was shown that COLLECT generates more recursive calls. Does more recursive call means more "strain" on the database?

    Thanks,

    Raman
  • 45736
    45736 Member Posts: 153
    My apologies...three solutions...

    the third one is:

    SQL> ed
    Wrote file afiedt.buf

    1 select person from project6030
    2 where id_state in (2, 3, 4)
    3 group by person
    4* having count(distinct id_state) = 3
    SQL> /

    Raman
  • William Robertson
    William Robertson Member Posts: 9,568 Bronze Crown
    So in our people/states example with an explicitly specified set of states, that becomes:
    WITH projects AS
    ( select 'Carl' person, 3 id_state from dual union all
    select 'Carl', 4 from dual union all
    select 'Joe', 3 from dual union all
    select 'Mark', 2 from dual union all
    select 'Mark', 3 from dual union all
    select 'Mark', 4 from dual union all
    select 'Paul', 1 from dual union all
    select 'Paul', 2 from dual union all
    select 'Sam', 2 from dual union all
    select 'Sam', 3 from dual )
    , people AS
    ( SELECT DISTINCT person FROM projects )
    --
    SELECT person
    FROM people p
    WHERE NOT EXISTS
    ( SELECT column_value FROM TABLE(INTEGER_TT(2,3,4))
    MINUS
    SELECT id_state FROM projects WHERE person = p.person );
    Nice.
  • Satyaki_De
    Satyaki_De Member Posts: 7,081
    William i've tried this solution and it shows --
    satyaki>
    satyaki>CREATE TYPE integer_tt AS TABLE OF INTEGER;
      2  /
    
    Type created.
    
    satyaki>
    satyaki>
    satyaki>WITH project AS
      2        ( select 'Carl' person, 3 id_state from dual union all
      3          select 'Carl', 4 from dual union all
      4          select 'Joe',  3 from dual union all
      5          select 'Mark', 2 from dual union all
      6          select 'Mark', 3 from dual union all
      7          select 'Mark', 4 from dual union all
      8          select 'Paul', 1 from dual union all
      9          select 'Paul', 2 from dual union all
     10          select 'Sam',  2 from dual union all
     11          select 'Sam',  3 from dual )
     12  SELECT person
     13  FROM   project
     14  GROUP  BY person
     15  HAVING integer_tt(2,3,4) SUBMULTISET OF CAST(COLLECT(id_state) AS integer_tt) ;
    HAVING integer_tt(2,3,4) SUBMULTISET OF CAST(COLLECT(id_state) AS integer_tt)
                             *
    ERROR at line 15:
    ORA-00920: invalid relational operator
    
    
    satyaki>
    Is it because i'm using Oracle 9i, so it is giving me an error?

    Regards.

    Satyaki De.
This discussion has been closed.