Using count as a condition in Oracle

Question

I have two queries: q1and q2. I want to return columns from q1when q2there are no rows. Example:

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

Usually I just use JOIN, but in this case I have no associated keys.

What is the best way to do this in Oracle?

Answer 1

I assume that these queries are completely independent, for example:

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);

Now you have a query with a query q2that selects the counter table_q2 and cross-connects it to table_q1with the condition q2.cnt = 0, so q1 selects only records if q2 count is! = 0.

The following select statement does not return 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 it 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 ....) 

. - , NOT EXISTS, (.. table.column_name = table2.column_name). .

SELECT . , (, , ). , - , .

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

Answer 3

, -

SELECT *
  FROM TABLE1
 WHERE DECODE((SELECT COUNT(T2.SOME_COLUMN)
                FROM TABLE2 T2
               WHERE T2.CONDITION_COLUMN = 'SOM_VAL'),
              0,
              'FALSE',
              'TRUE') = 'TRUE'

DECODE

will count the number of a certain column. In case it is ZERO, it will return false and query will return nothing or in case it returns anything more than ZERO, it will return TRUE and query will return values.

,

Answer 4

• Write a query that includes COUNT and GROUP BY without trying to filter out COUNT (x) = 0. You should see zeros in your result set. This is what you want to eliminate.

• Add HAVING clause: HUNING COUNT (x) <> 0

Answer 5

If the tables are actually joined in some field (let them call it idfor both), it’s worth building a query like

SELECT ... FROM table WHERE id NOT IN (SELECT id FROM table2 WHERE ...)

Answer 6

Check this request

Tested

select * from table1 where (SELECT count() FROM table2)=0