SQL problem

I have noticed this interesting thread on the Oracle-l mailing list about a “seemingly simple SQL requirement”. The question was about how to write a query that returned NO rows from a table t - with an id column - whenever a given list of id’s contained at least one id that did not exist in the table t. To illustrate:

scott@EDDEV> create table t(id number);

Table created.

scott@EDDEV> insert into t values(1);

1 row created.

scott@EDDEV> insert into t values(2);

1 row created.

scott@EDDEV> select * from t;

        ID
----------
         1
         2

scott@EDDEV> select * from t where id in (1,2);

        ID
----------
         1
         2

scott@EDDEV> select * from t where id in (1,2,3);

        ID
----------
         1
         2

The requirement was that the last query above should not return any rows because the id 3 does not exist in the table t. Here are the three solutions that were presented and my take on each one:

First, this query:

scott@EDDEV> select id
  2  from (select id, count(id) over () rows_in_set
  3       from t
  4       where id in (1, 2, 3))
  5  where rows_in_set = 3;

no rows selected

Neat. But there are two issues with this query. First, you need to know how many id’s you have in your id list before running the query, that requires another hard coded input to the query. Second, it does not work as expected if there are duplicate id’s in the id list:

scott@EDDEV> select id
  2  from (select id, count(id) over () rows_in_set
  3       from t
  4       where id in (1,2,2))
  5  where rows_in_set = 3;

no rows selected

Based on the requirement, since all id numbers in the id’s list exist in t, the above query should have returned rows 1 and 2.

On to the second offered solution:

scott@EDDEV> select * from t
  2  where exists(select 1 from t where id=1)
  3  and   exists(select 1 from t where id=2)
  4  and   exists(select 1 from t where id=3);

no rows selected

Works great, but does not scale (no binding). What if instead of 1,2 and 3, we had 100 id’s?

The last solution was:

scott@EDDEV> with
  2  x as
  3    (select
  4      length(
  5        replace(
  6          translate('&&1','1234567890','           '),
  7          ' ')
  8        )+1 c1
  9     from dual)
 10  ,y as
 11    (select count(*) c2
 12     from t where id in (&1))
 13  select t.*
 14    from t, x a, y b
 15   where id in (&1)
 16     and a.c1 = b.c2;
Enter value for 1: 1,2
old   6:         translate('&&1','1234567890','           '),
new   6:         translate('1,2','1234567890','           '),
old  12:    from t where id in (&1))
new  12:    from t where id in (1,2))
old  15:  where id in (&1)
new  15:  where id in (1,2)

        ID
----------
         1
         2

scott@EDDEV> define 1 = 1,2,3
scott@EDDEV> /
old   6:         translate('&&1','1234567890','           '),
new   6:         translate('1,2,3','1234567890','           '),
old  12:    from t where id in (&1))
new  12:    from t where id in (1,2,3))
old  15:  where id in (&1)
new  15:  where id in (1,2,3)

no rows selected

Very nice. Basically, the concept is the same as the first solution but the query is written in a more generic way. The “x” query returns the number of elements in the list of id’s. That’s the hardcoded value in the first solution above. Like the first solution, this solution does not work as expected if there are duplicate id’s in the id list:

scott@EDDEV> define 1 = 2,2
scott@EDDEV> /
old   6:         translate('&&1','1234567890','           '),
new   6:         translate('2,2','1234567890','           '),
old  12:    from t where id in (&1))
new  12:    from t where id in (2,2))
old  15:  where id in (&1)
new  15:  where id in (2,2)

no rows selected

The above query also assumes that the column is always numeric and the delimiter is always a comma (otherwise the “y” query will return wrong results.) Also note that the “WITH” clause is only supported in Oracle version 9i and above.

Possibly related:

Tagged | Post a Comment