oracle与join相反的函数,PL/SQL Challenge 每日一题：2016-11-2 反连接(ANTI JOIN)

最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正)，其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。

每两周的优胜者可获得itpub奖励的技术图书一本。

以往旧题索引：

http://www.itpub.net/forum.php?m ... eid&typeid=1808

原始出处：

http://www.plsqlchallenge.com/

作者：        Kim Berg Hansen

运行环境：SQLPLUS, SERVEROUTPUT已打开

注：本题给出答案时候要求给予简要说明才能得到奖品

我有如下的作者和书的表：

create table plch_authors (

id       integer primary key

, name     varchar2(20)

)

/

create table plch_books (

id       integer primary key

, title    varchar2(20)

, author   integer references plch_authors

)

/

insert into plch_authors values (10, 'Johnson')

/

insert into plch_authors values (20, 'McDowel')

/

insert into plch_authors values (30, 'Wallace')

/

insert into plch_books values (100, 'Vacation on Mars'  , 10)

/

insert into plch_books values (200, 'Guide to Nirvana'  , 30)

/

insert into plch_books values (300, 'Swami Lecture Book', 30)

/

commit

/

我想要一个清单，哪些作者还没有写书——也即，哪些作者的ID没有出现在books表的AUTHOR列中。

哪些选项给我一个没写书的作者清单，输出如下：

ID NAME

---------- --------------------

20 McDowel

(A)

select a.id, a.name

from plch_authors a

left anti join plch_books b

on b.author = a.id

order by a.id

/

(B)

select a.id, a.name

from plch_authors a

left outer join plch_books b

on b.author = a.id

where b.id is null

order by a.id

/

(C)

select a.id, a.name

from plch_authors a

left outer join plch_books b

on b.author = a.id

and b.id is null

order by a.id

/

(D)

select a.id, a.name

from plch_authors a

join plch_books b

on b.author = a.id

where b.id is null

order by a.id

/

(E)

select a.id, a.name

from plch_authors a

, plch_books b

where b.author(+) = a.id

and b.id is null

order by a.id

/

(F)

select a.id, a.name

from plch_authors a

, plch_books b

where b.author(+) = a.id

and b.id(+) is null

order by a.id

/

(G)

select a.id, a.name

from plch_authors a

where not exists (

select null

from plch_books b

where b.author = a.id

)

order by a.id

/

(H)

select a.id, a.name

from plch_authors a

where a.id not in (

select b.author

from plch_books b

)

order by a.id

/