SQL problem
Came across this SQL problem in Eddie Awad’s blog, where he gives a solution to a problem posted to the Oracle-l mailing list.
I thought, this would be so much easier to solve if we were allowed to model the complete problem domain in the database, e.g.:
— create the data set
SQL> create table t (id number);
Table created.
SQL> insert into t values (1);
1 row created.
SQL> insert into t values (2);
1 row created.
SQL> select * from t;
ID
———-
1
2
— create the query set
SQL> create table u (id number);
Table created.
SQL> insert into u values (1);
1 row created.
SQL> insert into u values (2);
1 row created.
SQL> insert into u values (3);
1 row created.
SQL> select * from u;
ID
———-
1
2
3
SQL> select * from t
2 where id in (select id from u)
3 and not exists (select id from u minus select id from t);
no rows selected
— correct: the complete query set was not found in the data set
SQL> insert into t values (3);
1 row created.
SQL> select * from t
2 where id in (select id from u)
3 and not exists (select id from u minus select id from t);
ID
———-
1
2
3
— correct: the complete query set was found in the data set
The query could be further improved by changing the “where id in ()” to a “where exists ()”.
To make the query table usable by multiple sessions simultaneously, it can be created as a global temporary table (and populated whenever a query is needed), or a “group” distinguisher could be added to it.
About Jeffrey Kemp
Application Designer & Developer at Oracle specialising in Oracle APEX (Application Express), Oracle SQL and PL/SQL. Oracle ACE Alumni ♠️. Piano player, father, husband, Christian. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.