SQL query : how to check existence of multiple rows with one query

Question

I have this table MyTable:

PROG    VALUE
-------------
1       aaaaa
1       bbbbb
2       ccccc
4       ddddd
4       eeeee

now I'm checking the existence of a tuple with a certain id with a query like

SELECT COUNT(1) AS IT_EXISTS
FROM MyTable
WHERE ROWNUM = 1 AND PROG = {aProg}

For example I obtain with aProg = 1 :

IT_EXISTS
---------
   1

I get with aProg = 3 :

IT_EXISTS
---------
   0

The problem is that I must do multiple queries, one for every value of PROG to check.

What I want is something that with a query like

SELECT PROG, ??? AS IT_EXISTS
FROM MyTable
WHERE PROG IN {1, 2,3, 4, 5} AND {some other condition}

I can get something like

PROG     IT_EXISTS
------------------
   1        1
   2        1
   3        0
   4        1
   5        0

The database is Oracle...

Hope I'm clear

regards

Paolo

Answer 1

Take a step back and ask yourself this: Do you really need to return the rows that don't exist to solve your problem? I suspect the answer is no. Your application logic can determine that records were not returned which will allow you to simplify your query.

SELECT PROG
FROM MyTable
WHERE PROG IN (1, 2, 3, 4, 5)

If you get a row back for a given PROG value, it exists. If not, it doesn't exist.

Update:

In your comment in the question above, you stated:

the prog values are from others tables. The table of the question has only a subset of the all prog values

This suggests to me that a simple left outer join could do the trick. Assuming your other table with the PROG values you're interested in is called MyOtherTable, something like this should work:

SELECT a.PROG,
CASE WHEN b.PROG IS NOT NULL THEN 1 ELSE 0 END AS IT_EXISTS
FROM MyOtherTable AS a
LEFT OUTER JOIN MyTable AS b ON b.PROG = a.PROG

A WHERE clause could be tacked on to the end if you need to do some further filtering.