CODEWITHSUNDEEP
sqloracle-database
Dale
Dale

Reputation: 5825

Eliminating "not in" for SQL command

I would like to take a sample of an Oracle table, but not include entries from another table. I have a query that currently works, but I'm pretty sure it will blow-up when the sub-select gets more than 1000 records.

select user_key from users sample(5)
where active_flag = 'Y'
and user_key not in (
    select user_key from user_validation where validation_state <> 'expired'
);

How could this be re-written without the not in. I thought of using minus, but then my sample size would keep going down as new entries were added to the user_validation table.

Upvotes: 0

Views: 124

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

You can do this with a left outer join:

select *
from (select u.user_key,
             count(*) over () as numrecs
      from users u left outer join
           user_validation uv
           on u.user_key = uv.user_key and
              uv.validation_state <> 'expired'
      where u.active_flag = 'Y' and uv.user_key is null
     ) t
where rownum <= numrecs * 0.05

You are using the sample clause. It is not clear if you just want the non-matches in the 5% you choose or if you want 5% of the data that is non-matches. This is the latter.

EDIT: Added example based on author's comment:

select user_key from (
  select u.user_key, row_number() over (order by dbms_random.value) as randval
  from users u 
    left outer join user_validation uv 
    on u.user_key = uv.user_key 
    and uv.validation_state <> 'expired'
  where u.active_flag = 'Y' 
  and uv.user_key is null
) myrandomjoin where randval <=100;

Upvotes: 3

wildplasser
wildplasser

Reputation: 44250

select us.user_key
from users us -- sample(5)
where us.active_flag = 'Y'
and NOT EXISTS (
    SELECT *
    from user_validation nx
    where nx.user_key = us.user_key
    AND nx.validation_state <> 'expired'
    );

BTW: I commented-out the sample(5) because I don't know what it means. (I strongly believe that it is not relevant, though)

Upvotes: 2

Pradeep Pati
Pradeep Pati

Reputation: 5919

select u.user_key from users u, user_validation uv
where u.active_flag = 'Y'
and u.user_key=uv.user_key 
uv.validation_state= 'expired';

This was a double negation query, x not in list of non expired ids, which is equivalent to x is in the list of expired IDs, which is what I did, in addition to changing the subquery to a join.

Upvotes: 1

Related Questions