How to get details for rows that do not exists in

Question

I was caught up with an strange situation. Here it is,

We have table with 2 million records where MSISDN is of type string ,unique and not null. I was requested to get details of 300 known MSISDN's and so the query used is,

select * from table_name 
where msisdn in ('msisdn1','msisdn2',......'msisdn300')

but unfortunately the above query returns only 200 count. How to find out those 100 that do no exists from 300 given in the query?

I can use only select query due to limited privileges.

Please advice.

Answer 1

First of all the MSISDN is a primary key (unique + not null), So there is no chance to be nil.

You call use the following query,

Select * from table name where MSISDN is not null limit 300;

Answer 2

I think what you're asking for is possible, but it requires a little bit of effort.

Firstly, I don't believe there's any way to find what items within an IN clause don't match anything in a table. So instead I'm afraid you'll have to copy that part of your query and modify it.

At the moment, your query looks something like

SELECT * FROM my_table WHERE id IN ('id1','id2','id3','id4','id5','id6')

Assuming that none of your MSISDNs have commas and/or single-quotes in them, what you can do is to:

• take the text of the IN (...) part of your query,
• remove all of the single quotes,
• add a comma to the start and the end,
• surround the whole string in single quotes.

The list of six ids in my example above would then become

',id1,id2,id3,id4,id5,id6,'

With all of the IDs in a single string, we can run a query such as the following:

VARIABLE ids_string VARCHAR2(4000);
EXEC :ids_string := ',id1,id2,id3,id4,id5,id6,';

WITH comma_posns AS (SELECT level AS comma_pos
                       FROM DUAL
                      WHERE SUBSTR(:ids_string, level, 1) = ','
                       CONNECT BY LEVEL <= LENGTH(:ids_string)),
     starts_ends AS (SELECT comma_pos AS start_pos,
                            LEAD(comma_pos, 1, NULL) OVER (ORDER BY comma_pos)
                                      AS end_pos
                       FROM comma_posns),
     ids AS (SELECT SUBSTR(:ids_string, start_pos + 1, end_pos - start_pos - 1)
                    AS id
               FROM starts_ends
              WHERE end_pos IS NOT NULL)
SELECT id
  FROM ids
 WHERE id NOT IN (SELECT id FROM my_table);

I put the list of IDs in a bind variable named :ids_string so that I could refer to it multiple times throughout the query.

The comma_posns subquery lists all of the positions within the string that commas appear at. starts_ends then rearranges this into pairs of comma positions, and then ids uses these start and end positions to pick the IDs out of the string. Finally, we select all of the IDs that are in this string but not in the table.

I ran the above query using the following sample data:

SQL> select id from my_table;

ID
--------------------
id1
id1a
id4
id6

When I ran this query, I got the following result:

ID
--------------------------------------------------------------------------------
id2
id3
id5

If your IDs string is particularly long (i.e. over 4000 characters' worth), you may have to split it up into parts that are 4000 or fewer characters long.

Answer 3

You can build up a "virtual" table with all the IDs you are looking for, and then do an outer join on that with your real table.

with to_search as (
    select 'msisdn1' as msisdn from dual 
    union all 
    select 'msisdn1' from dual
    union all
    select 'msisdn2' from dual
    union all
    select 'msisdn2' from dual
    ...
    (you get the picture)
    ...
    select 'msisdn3000' from dual
)
SELECT s.msisdn, 
       nvl(mt.msisdn, 'not found')
FROM to_search s
  LEFT JOIN my_table mt ON mt.msisdn = s.msisdn 

For all IDs that are not present in my_table you'll see a "not found"