CODEWITHSUNDEEP
sqloracle-databaseselect
Anand Kadhi
Anand Kadhi

Reputation: 1878

Select where record does not exists

I am trying out my hands on oracle 11g. I have a requirement such that I want to fetch those id from list which does not exists in table.

For example:

SELECT * FROM STOCK
where  item_id in ('1','2'); // Return those records where result is null

I mean if item_id '1' is not present in db then the query should return me 1.

How can I achieve this?

Upvotes: 0

Views: 66

Answers (4)

collapsar
collapsar

Reputation: 17238

A primary concern of the OP seems to be a terse notation of the query, notably the set of values to test for. The straightforwwrd recommendation would be to retrieve these values by another query or to generate them as a union of queries from the dual table (see the other answers for this).

The following alternative solution allows for a verbatim specification of the test values under the following conditions:

  1. There is a character that does not occur in any of the test values provided ( in the example that will be - )

  2. The number of values to test stays well below 2000 (to be precise, the list of values plus separators must be written as a varchar2 literal, which imposes the length limit ). However, this should not be an actual concern - If the test involves lists of hundreds of ids, these lists should definitely be retrieved froma table/view.

Caveat

Whether this method is worth the hassle ( not to mention potential performance impacts ) is questionable, imho.

Solution

The test values will be provided as a single varchar2 literal with - separating the values which is as terse as the specification as a list argument to the IN operator. The string starts and ends with -.

'-1-2-3-156-489-4654648-'

The number of items is computed as follows:

select cond, regexp_count ( cond, '[-]' ) - 1 cnt_items  from (select '-1-2-3-156-489-4654648-' cond from dual)

A list of integers up to the number of items starting with 1 can be generated using the LEVEL pseudocolumn from hierarchical queries:

select level from dual connect by level < 42;

The n-th integer from that list will serve to extract the n-th value from the string (exemplified for the 4th value) :

select substr ( cond, instr(cond,'-', 1, 4 )+1, instr(cond,'-', 1, 4+1 ) - instr(cond,'-', 1, 4 ) - 1 ) si from (select cond, regexp_count ( cond, '[-]' ) - 1 cnt_items  from (select '-1-2-3-156-489-4654648-' cond from dual) );

The non-existent stock ids are generated by subtracting the set of stock ids from the set of values. Putting it all together:

    select substr ( cond, instr(cond,'-',1,level )+1, instr(cond,'-',1,level+1 ) - instr(cond,'-',1,level ) - 1 ) si 
      from (
              select cond
                   , regexp_count ( cond, '[-]' ) - 1 cnt_items
                from (
                        select '-1-2-3-156-489-4654648-' cond from dual
                     )
           ) 
connect by level <= cnt_items + 1
     minus
    select item_id from stock
         ;

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94894

First create the list of possible IDs (e.g. 0 to 99 in below query). You can use a recursive cte for this. Then select these IDs and remove the IDs already present in the table from the result:

with possible_ids(id) as
(
  select 0 as id from dual
  union all
  select id + 1 as id from possible_ids where id < 99
)
select id from possible_ids
minus
select item_id from stock;

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can use a LEFT JOIN to an in-line table that contains the values to be searched:

SELECT t1.val
FROM (
   SELECT '1' val UNION ALL SELECT '2'
) t1
LEFT JOIN STOCK t2 ON t1.val = t2.item_id
WHERE t2.item_id IS NULL

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You need to store the values in some sort of "table". Then you can use left join or not exists or something similar:

with ids as (
      select 1 as id from dual union all
      select 2 from dual
     )
select ids.id
from ids
where not exists (select 1 from stock s where s.item_id = ids.id);

Upvotes: 2

Related Questions