CODEWITHSUNDEEP
sqloracle-database
The Light
The Light

Reputation: 27011

How to use Select Exists in Oracle?

What is the equivalent of the below 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 want an oracle query where exists is used and it returns 0 or 1 like above.

Upvotes: 10

Views: 78571

Answers (5)

Leon
Leon

Reputation: 198

You can use one of the following queries: (the first ones is more performant)

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;

Upvotes: -1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115510

This would show the same output. Just removed the CAST and added a FROM dual as Oracle doesn't 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 at SQL-Fiddle

Upvotes: 16

Frank Schmitt
Frank Schmitt

Reputation: 30765

You could also use MAX with CASE:

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

Upvotes: 2

Florin Ghita
Florin Ghita

Reputation: 17643

The equivalent would be:

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

Upvotes: 15

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

You could write it:

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

This will return 0-1 and the optimizer gets that the query is to be optimized for first-row access.

Upvotes: 7

Related Questions