SQL & PL/SQL

Hi all,

I have been working on a report I need to produce that involves using a NOT IN statement in my sql to pull back the report data correctly. However, as I have found, NOT IN is very slow and does not return the data in a realistic time frame. So, I did some checking, and found that I could use an alternative; LEFT OUTER JOIN, where right table property is null, instead. But, I have come across a problem, in that within my NOT IN statement, i restrict it to use only a subset of the not in table, using a reference to another subquery in the same statement. However, it does not seem possible to fully transpose the NOT IN to a left outer join as the reference requires a JOIN, and I cant work out how to put the 2 different JOIN types together in one statement:

Correct working NOT IN statement:

SELECT data_1.id
,data_1.category
,data_2.value
FROM (SELECT id
,category
FROM D1) data_1
JOIN
(SELECT fk_id
,VALUE
,id
FROM D2) data_2 ON data_1.id = data_2.fk_id
WHERE data_2.id not in (select id
from D3
where data_1.category = D3.category
and id is not null)

***** bit in bold is restriction to subset of D3



Not quite working LEFT OUTER JOIN statement:

SELECT
data_1.id,
data_1.category,
data_2.value,
invalid_lookup.category,
invalid_lookup.value
from
(SELECT id
,category
FROM D1) data_1
JOIN
(SELECT fk_id
,VALUE
,id
FROM D2) data_2 ON data_1.id = data_2.fk_id
LEFT OUTER JOIN
(select id
,category
from D3) invalid_lookup on data_2.id = invalid_lookup.id
WHERE invalid_data_2.id is null

FYI D3 (invalid_lookup) table is structured as follows:

CATEGORY, ID
A, 1
A, 2
A, 3
A, 4
B, 5
B, 6
B, 7
C, 8
C, 9
C, 10
C, 11
C, 12

So, basically, what I cannot work out how to do is add in the bit in bold in the NOT IN statement to the LEFT OUTER JOIN statement somewhere, to only restrict the invalid_lookup table (D3) to a specifc subset based on a category e.g I want to return all rows in data_1 and data_2 which dont have an ID matching the category they are supposed to be in e.g if i have a row that had category B (5,6,7) but an ID of 11 it should be reported.