SQL 如何在 Oracle 中使用 Select Exists？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16562162/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use Select Exists in Oracle?
提问by The Light
What is the equivalent of the below SQL Queryin Oracle?
什么是下面的等效SQL Query的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.
我只想要一个 oracle 查询 where exists 被使用，它像上面一样返回 0 或 1。
回答by Florin Ghita
The equivalent would be:
相当于：
select count(*)
from dual
where exists (SELECT * FROM theTable where theColumn like 'theValue%')
回答by ypercube??
This would show the same output. Just removed the CASTand added a FROM dualas Oracle doesn't allow queries with SELECTand without FROM:
这将显示相同的输出。刚刚删除CAST并添加了一个，FROM dual因为 Oracle 不允许有SELECT和没有的查询FROM：
SELECT
CASE WHEN EXISTS(SELECT * FROM theTable where theColumn like 'theValue%')
THEN 1
ELSE 0
END
FROM dual ;
Tested at SQL-Fiddle
在SQL-Fiddle测试
回答by Vincent Malgrat
You could write it:
你可以这样写：
SELECT COUNT(*) FROM theTable WHERE theColumn LIKE 'theValue%' AND ROWNUM = 1
This will return 0-1and the optimizer gets that the query is to be optimized for first-row access.
这将返回0-1并且优化器得知查询将针对第一行访问进行优化。
回答by Frank Schmitt
You could also use MAX with CASE:
您还可以将 MAX 与 CASE 一起使用：
SELECT MAX(
CASE
WHEN theColumn like 'theValue%' THEN 1
ELSE
0
END)
AS BIT
FROM theTable
回答by Leon
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;
