Query with empty or one row only

I’m using an oracle DB and I need to know if certain value is on myTable (it can be 0-N times), but i’m using a software that does some tasks with each row of the query output, so, my result should be empty when I have no value in myTable (so the software will do nothing) or one row when I have 1 or more values in myTable (so the software will do what it needs to do one time only). My problem is:

If I use: SELECT 1 FROM myTable WHERE col = value it returns empty for 0 values, but return N for N values and the sofware runs N times when it should only run once.

If I use: SELECT max(1) FROM myTable WHERE col = value it returns 1 row for N values, but it also returns 1 row for 0 values (in this case the row has NULL, but still trigger my software to do the tasks that should be done only with 1 or more values).

Someone know how can I make a single query that return empty for 0 values and 1 row for N values? Thanks.

Answer

EXISTS might be one choice; its benefit is that it stops searching as soon as it finds the first occurrence of what you’re looking for.

An example based on Scott’s EMP table: there are employees who work in departments 10, 20 and 30 so – if I search for any of them, query should return 1. For any other department (e.g. 99), it shouldn’t return anything.

Sample data:

SQL> select deptno, ename from emp order by deptno;

    DEPTNO ENAME
---------- ----------
        10 CLARK
        10 KING
        10 MILLER
        20 JONES
        20 FORD
        20 ADAMS
        20 SMITH
        20 SCOTT
        30 WARD
        30 TURNER
        30 ALLEN
        30 JAMES
        30 BLAKE
        30 MARTIN

14 rows selected.

EXISTS examples:

SQL>  select 1
  2   from dual
  3   where exists (select null from emp
  4                 where deptno = 10);

         1
----------
         1

SQL>  select 1
  2   from dual
  3   where exists (select null from emp
  4                 where deptno = 99);

no rows selected