Oracle:检查其他表中是否存在行

另一种选择:

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

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

如果你在PK和FK上有索引,你就可以进行表扫描和索引查找。

K

使用:

   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

DISTINCT 以确保仅显示唯一的行。

A. join id=1

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

这个 group by 确保每个id只有一行。和 count(distinct t2.ref_id)

编辑:您可以不使用 分组 ,但我怀疑这会让事情变得更容易:

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