How to use Select Exists in Oracle?

Question

What is equivalent to the following SQL Query in Oracle ?

 SELECT CAST( CASE WHEN EXISTS(SELECT * FROM theTable where theColumn like 'theValue%') THEN 1 ELSE 0 END AS BIT) 

I just need an oracle request where exists, and it returns 0 or 1 as above.

Answer 1

Equivalent:

 select count(*) from dual where exists (SELECT * FROM theTable where theColumn like 'theValue%') 

Answer 2

This will show the same result. Just removed CAST and added FROM dual , since Oracle does not allow queries with SELECT and without FROM :

 SELECT CASE WHEN EXISTS(SELECT * FROM theTable where theColumn like 'theValue%') THEN 1 ELSE 0 END FROM dual ; 

Tested with SQL-Fiddle

Answer 3

You can write this:

 SELECT COUNT(*) FROM theTable WHERE theColumn LIKE 'theValue%' AND ROWNUM = 1 

This will return 0-1 , and the optimizer will get the query to be optimized to access the first row.

Answer 4

You can also use MAX with CASE:

 SELECT MAX( CASE WHEN theColumn like 'theValue%' THEN 1 ELSE 0 END) AS BIT FROM theTable 

Answer 5

You can use one of the following queries: (the first are more effective)

 SELECT H.TABLE_ID, H.OTHER_FIELD, (SELECT 'YES' FROM DUAL WHERE EXISTS (SELECT 'X' FROM TABLE_DETAIL DT WHERE DT.TABLE_ID = H.TABLE_ID) ) WITH_DETAIL FROM TABLE_HEADER H; 

---

 SELECT H.TABLE_ID, H.OTHER_FIELD, CASE WHEN EXISTS(SELECT * FROM IMTS.DETAIL_TABLE DT WHERE DT.TABLE_ID=H.TABLE_ID) THEN 'Y' ELSE 'N' END WITH_DETAIL FROM HEADER_TABLE H; 

 SELECT H.TABLE_ID, H.OTHER_FIELD, NVL2(DT.SOME_NOTNULL_FIELD, 'YES','NO') WITH_DETAIL FROM TABLE_HEADER H LEFT JOIN TABLE_DETAIL DT ON DT.TABLE_ID=H.TABLE_ID AND ROWNUM<2;