Using count as a condition in Oracle

Question

I have two queries, q1 and q2. I want to return columns from q1 when q2 has no rows. Example:

select a, b, c from t1 where
count(select d, e, f from t2 where ...) == 0
and ...

Normally, I would just use a JOIN, but in this case, I have no related keys.

What is the best way to do this in Oracle?

Answer 1

I assume that those queries are entirely independant, like so:

create table table_q1 (
  id  number,
  txt varchar2(10)
);

insert into table_q1 values ( 1, 'This');
insert into table_q1 values ( 2, 'data');
insert into table_q1 values ( 3, 'only');
insert into table_q1 values ( 4, 'selected');
insert into table_q1 values ( 5, 'if');
insert into table_q1 values ( 6, 'other');
insert into table_q1 values ( 7, 'query''s');
insert into table_q1 values ( 8, 'count');
insert into table_q1 values ( 9, 'greater');
insert into table_q1 values (10, 'zero');

create table table_q2 (
  id  number
);

insert into table_q2 values (1);
insert into table_q2 values (2);
insert into table_q2 values (3);
insert into table_q2 values (4);

You can now have a with-query q2 that selects the count of table_q2 and cross join it to table_q1 with the condition q2.cnt = 0 so that q1 only selects records if q2's count is != 0.

The following select statement returns no records:

with q2 as (select count(*) cnt from table_q2 where id > 2)
select q1.* from table_q1 q1, q2
where q2.cnt = 0
order by q1.id;

But this one does:

with q2 as (select count(*) cnt from table_q2 where id > 1000)
select q1.* from table_q1 q1, q2
where q2.cnt = 0
order by q1.id;

Answer 2

select <columns> 
  from table 
 where not exists (select <columns> 
                     from table2 
                     where ....) 

should work. If there were some relationship between the inner query and the outer query, you would just add an additional predicate to the NOT EXISTS subquery that expressed that relationship (i.e. table.column_name = table2.column_name). But there is no need to make the subquery correlated.

You also don't need to specify the column names in the SELECT list of the subquery. It would only matter if adding the columns changed the query plan (say, by forcing the optimizer to query the table rather than using a covering index). You'll get the same result if you use something like this and it may be slightly faster.

select <columns> 
  from table 
 where not exists (select 1 
                     from table2 
                     where ....)