Newbie: Simple select with 'IN ALL'

569964 · Aug 31, 2007 1:29AM

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 · Aug 31, 2007 1:36AM

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 · Aug 31, 2007 5:45AM

> 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?

Vadim Tropashko-Oracle · Aug 31, 2007 2:27PM

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 · Aug 31, 2007 2:37PM

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 · Aug 31, 2007 2:43PM

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 · Sep 1, 2007 3:17AM

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 · Sep 1, 2007 3:30AM

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 · Sep 1, 2007 5:04AM

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 · Sep 3, 2007 1:39AM

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.

Forum Stats

Quick Links

Categories

Newbie: Simple select with 'IN ALL'

Comments

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

Newbie: Simple select with 'IN ALL'

Comments