Oracle：检查其他表中是否存在行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1917089/
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
Oracle: Check if rows exist in other table
提问by Peter Lang
I've got a query joining several tables and returning quite a few columns.
我有一个连接多个表并返回相当多列的查询。
An indexed column of another table references the PK of one of these joined tables. Now I would like to add another column to the query that states if at least one row with that ID exists in the new table.
另一个表的索引列引用这些连接表之一的 PK。现在，我想向查询添加另一列，说明新表中是否至少存在具有该 ID 的一行。
So if I have one of the old tables
所以如果我有一张旧桌子
ID
1
2
3
and the new table
和新表
REF_ID
1
1
1
3
then I'd like to get
那么我想得到
ID REF_EXISTS
1 1
2 0
3 1
I can think of several ways to do that, but what is the most elegant/efficient one?
我可以想到几种方法来做到这一点，但最优雅/最有效的方法是什么？
EDITI tested the performance of the queries provided with 50.000 records in the old table, every other record matched by two rows in the new table, so half of the records have REF_EXISTS=1.
编辑我测试了在旧表中提供 50.000 条记录的查询的性能，每隔一条记录与新表中的两行匹配，因此一半的记录具有 REF_EXISTS=1。
I'm adding average results as comments to the answers in case anyone is interested. Thanks everyone!
如果有人感兴趣，我将平均结果添加为答案的评论。谢谢大家！
回答by Shannon Severance
Another option:
另外一个选项：
select O.ID
, case when N.ref_id is not null then 1 else 0 end as ref_exists
from old_table o
left outer join (select distinct ref_id from new_table) N
on O.id = N.ref_id
回答by Khb
I would:
我会：
select distinct ID,
case when exists (select 1 from REF_TABLE where ID_TABLE.ID = REF_TABLE.REF_ID)
then 1 else 0 end
from ID_TABLE
Provided you have indexes on the PK and FK you will get away with a table scan and index lookups.
如果您在 PK 和 FK 上有索引，您将可以进行表扫描和索引查找。
Regards K
问候 K
回答by OMG Ponies
Use:
用：
SELECT DISTINCT t1.id,
CASE WHEN t2.ref_id IS NULL THEN 0 ELSE 1 END AS REF_EXISTS
FROM TABLE_1 t1
LEFT JOIN TABLE_2 t2 ON t2.ref_id = t1.id
Added DISTINCTto ensure only unique rows are displayed.
添加DISTINCT以确保仅显示唯一的行。
回答by Andomar
A joincould return multiple rows for one id, as it does for id=1in the example data. You can limit it to one row per id with a group by:
Ajoin可以为一个 id 返回多行，就像id=1在示例数据中所做的那样。您可以通过以下方式将每个 id 限制为一行：
SELECT
t1.id
, COUNT(DISTINCT t2.ref_id) as REF_EXISTS
FROM TABLE_1 t1
LEFT JOIN TABLE_2 t2 ON t2.ref_id = t1.id
GROUP BY t1.id
The group byensures there's only one row per id. And count(distinct t2.ref_id)will be 1 if a row is found and 0 otherwise.
在group by确保有每个ID只有一行。而count(distinct t2.ref_id)将1如果行被发现，否则为0。
EDIT: You can rewrite it without a group by, but I doubt that will make things easer:
编辑：您可以在没有 的情况下重写它group by，但我怀疑这会让事情变得更轻松：
SELECT
t1.id
, CASE WHEN EXISTS (
SELECT * FROM TABLE_2 t2 WHERE t2.ref_id = t1.id)
THEN 1 ELSE 0 END as REF_EXISTS
, ....
FROM TABLE_1 t1
