SQL - selecting items NOT IN tables

[ashcarrot]

I have a table of reports and assigned to each of them is a number of keywords

REPORT(id, description, etc)
KEYWORD(id, report_id, name)

i want to get reports where there aren't certain keywords

so something like

SELECT DISTINCT(report.id) from keyword WHERE keyword NOT IN (1707,13,15)

To get all the report_id's where there is not a keyword_id thats

ie
report keyword

1 1707
1 14
2 1
3 15

would return "2"


But the trouble is with this as theres may be more than one keyword it may return a report where there is that keyword attached....

Thanks

 

[stefanhei]

Hi.
an easy way would be:

select distinct id rep_id
from reports
minus
select distinct report_id rep_id
from keywords
where keyword_id in (1707,13,15)

i guess it is possible to find better solutions using correlated queries.

Stefan

 

[walkerci]

ashcarrot,

Stefan is correct, correlated sub-queries are the way to go.
Especially if REPORT or KEYWORD tables are large.

Assuming a 1:Many relationship between REPORT and KEYWORD, this should work.

select rep_id
from reports
where not exists
(select 'X' from keywords
where reports.id = keywords.rep_id
and keyword_id in (1707,13,15))

Be sure you have a unique index on
KEYWORD(id, report_id), so you don't end up doing table scans on KEYWORD table.

In general, avoid DISTINCT on anything but the smallest tables, especially in side sub-queries.
DISTINCT can force a full table scan and a sort.
This is can really slow down your server and your application.

Hope this helps,

Charles

 

[carp]

In any event, if you DO use the MINUS operator, then DON'T use DISTINCT at all; it's not necessary:

select id rep_id
from reports
minus
select report_id rep_id
from keywords
where keyword_id in (1707,13,15);

will give you a list of unique report id numbers.

Also, a correlated subquery may be less efficient if you DON'T have the unique index on the columns:
select id from report
minus
select report_id from keywords;
Statistics
----------------------------------------------------------
0 recursive calls
8 db block gets
2 consistent gets
0 physical reads
0 redo size
361 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed

select id from report r
where not exists
(select 'x' from keywords where report_id = r.id);
Statistics
----------------------------------------------------------
0 recursive calls
12 db block gets
3 consistent gets
0 physical reads
0 redo size
361 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Note that the correlated subquery took half again as many db block gets and consistent gets as the MINUS operator.

 

[R1972]

how abt this

Select id from reports where id not in(select report_id from keyword where keyword_id not in(1707, 13, 15))