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?
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;
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 ....)
maybe you can try something like this
SELECT *
FROM TABLE1
WHERE DECODE((SELECT COUNT(T2.SOME_COLUMN)
FROM TABLE2 T2
WHERE T2.CONDITION_COLUMN = 'SOM_VAL'),
0,
'FALSE',
'TRUE') = 'TRUE'
Here the nested query within the
DECODEHope it helps
Write a query that includes COUNT and GROUP BY without trying to filter out COUNT(x) = 0. You should see the zeros in your result set. That you want to eliminate.
Add a HAVING clause: HAVING COUNT(x) <> 0
If the tables are in fact joined on some field (let's name it id for both), it worth construct a query like
SELECT ... FROM table WHERE id NOT IN (SELECT id FROM table2 WHERE ...)
Check this query
Tested
select * from table1 where (SELECT count() FROM table2)=0
