主页 / dba / 问题 / 96856
Accepted
Mark Harrison
Asked: 2015-04-02 23:39:39 +0800 CST

Oracle：在具有外键的表中查找没有子项的行？

  • 772

假设我有两个具有外键关系的表。如何在“父”表中找到没有相应“子”行的行？

例如：

create table a(x number primary key);
create table b(x number);
alter table b add constraint b_fk foreign key(x) references a(x) enable;

insert into a values(1);
insert into b values(1);
insert into a values(2);
commit;

delete from a where x = 2; -- will succeed
delete from a where x = 1; -- will fail, because there is a child row.

select ??? as "rows with no children" from a;

rows with no children
---------------------
                    2
oracle foreign-key

2 个回答

  1. 表达这一点的语义上适当的方式是：

    select ...
from   a
where  not exists (
         select null
         from   b
         where  b.x = a.x)

    NOT EXISTS 使用反半连接，它允许优化器对表和连接基数非常敏感。

    • 4
  2. Best Answer

    鉴于缺乏样本表，我必须弥补......

    select * from parent_table
where parent_table.foreign_key_id in
(select parent_table.foreign_key_id
from parent_table
minus
select child_table.id
from child_table)

    parent_table.foreign_key_id 可能与 child_table.id 匹配的位置

    • 3

相关问题