Can I do this in Oracle? 2

[peterswan]

Hello,

I know you can run a subquery in SQL Server to see if a list from one table matches all that of a list from another table, but when I run this in Oracle, it seems to work only some of the time. For example, please note this query:

select * from csq_admin_lookup
where adm_lu_system = 'CCP'
and adm_lu_type = 'CDIV'
and adm_lu_description in (
select distinct cust_division
from csq_customers)

This query returns 92 records. Then when I try to pull everything from the admin table, I get 106 records:

select * from csq_admin_lookup
where adm_lu_system = 'CCP'
and adm_lu_type = 'CDIV'

Logic has it that the following query would return 14 results, because the results of the previous queries imply that there are 14 records in the admin table that do not exist in the customer table (notice the word NOT):

select * from csq_admin_lookup
where adm_lu_system = 'CCP'
and adm_lu_type = 'CDIV'
and adm_lu_description NOT in (
select distinct cust_division
from csq_customers)

But this last query returns 0 records, implying that all records in the admin table are found in the customer table, which contradicts what the previous 2 queries imply.

Does Oracle support this type of subquery, where you can compare two list of string data? How about comparing IDs?

Thanks,

Peter

 

[peterswan]

I went ahead and ran the following query:

select distinct cust_division
from csq_customers

and copied the results into Excel.

Then I created a new Excel column with each value, including the single quotes around the value and a comma at the end, and applied this logic to the entire column. I copied all of this data into the following query:

select * from csq_admin_lookup
where adm_lu_system = 'CCP'
and adm_lu_type = 'CDIV'
and adm_lu_description NOT in (
EXCEL DATA HERE, WITH SINGLE QUOTES AND COMMAS)

The query returned 14 rows. This shows that Oracle for some reason is missing these records when I run the string list within a subquery.

What could be wrong?

Thanks,

Peter

 

[cmmrfrds]

Create an inline view of the records you want to check against in the csq_customers table, then check for a NULL record on the join. There will be a null record on the csq_customer side of the join when the description does not equal. This should give the same results that you expect and will be much more efficient especially on larger tables.

select * from csq_admin_lookup A
Left Outer Join
(
select cust_division
from csq_customers)
Group By cust_division
) inview
ON A.adm_lu_description = inview.cust_division
where A.adm_lu_system = 'CCP'
and A.adm_lu_type = 'CDIV'
and inview.cust_division is null

 

[carp]

Is it possible that you have 14 rows where adm_lu_description has a null value? If so, that would explain why they are not being returned.

Try this:

select * from csq_admin_lookup o
where adm_lu_system = 'CCP'
and adm_lu_type = 'CDIV'
and NOT EXISTS (SELECT 'x' 
                  FROM csq_customers
                 WHERE cust_division =  o.adm_lu_description);

 

[peterswan]

Thanks, carp,

That worked great. No, there are no null values for adm_lu_description.

The query you gave me returned 14 records, each of them matching the query I ran when I inserted the actual values for the subquery, which means it works perfectly.

So, I guess this means that when I use IN when combined with a SELECT subquery in this fashion, Oracle will return the results fine. However, when I use NOT IN, Oracle will not work, and instead I need to use your EXISTS method.

Maybe someone can elaborate on this.

Thanks again

Peter

 

[dtboy]

Yes I can elaborate. NOT IN can be very dangerous.

create table master_tbl (
user_id number,
user_name varchar(30),
job_id number
)

create table code_tbl (
job_id number,
job_desc varchar(20)
)


insert into master_tbl values (1,'John',3);
insert into master_tbl values (2,'Sarah',4);
insert into master_tbl values (3,'Jane',4);
insert into master_tbl values (4,'Bill',1);
insert into master_tbl values (5,'George',2);
insert into master_tbl values (5,'LaToya',3);


insert into code_tbl values (1,'Salesman');
insert into code_tbl values (2,'CEO');
insert into code_tbl values (3,'Accountant');

select * from master_tbl where job_id IN (select job_id from code_tbl);
select * from master_tbl where job_id NOT IN (select job_id from code_tbl);

insert into code_tbl values (NULL,NULL);

select * from master_tbl where job_id IN (select job_id from code_tbl);
select * from master_tbl where job_id NOT IN (select job_id from code_tbl);


You never said whether the lookup table you were using had nulls for cust_division, just that your table didn't. I suspect it did. This is a wonderful old chestnut of database programming that fowled me up a few years ago: you can definitively say something is IN a set even if null is part of that set. You cannot definitely say something is NOT IN that set if that set includes a null. This is not an accident on the part of the people who wrote oracle, it's meant to behave that way.

Now run this:

select * from master_tbl where job_id NOT IN (select job_id from code_tbl where job_desc IS NOT NULL);

Cmmr's response is quite correct as well, however, in terms of the performance gains, I'm not sure it would be that different in the latest version of oracle because they've gotten quite good at decomposing the statements into their fastest running forms.

(example done in oracle 9i, YMMV in another DB)

 

[dtboy]

For the benefit of those who don't have the DB handy, following from the first select statement above:

select * from master_tbl where job_id IN (select job_id from code_tbl);
4 Bill 1
5 George 2
1 John 3
5 LaToya 3

select * from master_tbl where job_id NOT IN (select job_id from code_tbl);

2 Sarah 4
3 Jane 4

insert into code_tbl values (NULL,NULL);

(commit!)

select * from master_tbl where job_id IN (select job_id from code_tbl);

4 Bill 1
5 George 2
1 John 3
5 LaToya 3

select * from master_tbl where job_id NOT IN (select job_id from code_tbl);


0 row returned;

select * from master_tbl where job_id NOT IN (select job_id from code_tbl where job_desc IS NOT NULL);


2 Sarah 4
3 Jane 4

 

[SantaMufasa]

Excellent example, dtboy...Hava Star!

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]