mysql查询not in_mysql in和not in子查询实例讲解

首先有这样三张表：books表、authors表和authorbook

book表：

BookID

BookTitle

Copyright

12786

Java

1934

13331

MySQL

1919

14356

PHP

1966

15729

PERL

1932

16284

Oracle

1996

17695

Pl/SQL

1980

19264

JavaScript

1992

19354

www.java2s.com

1993

authors表

AuthID

AuthFN

AuthMN

AuthLN

1006

H

S.

T

1007

J

C

O

1008

B

E

1009

R

M

R

1010

J

K

T

1011

J

G.

N

1012

A

P

1013

A

W

1014

N

A

authorbook表

AuthID

BookID

1009

12786

1006

14356

1008

15729

1011

15729

1014

16284

1010

17695

1012

19264

1012

19354

现在我们开始in和not in子查询实例

1.获取book表中bookid为12786或14356或17695或19354的数据：

select *from book where bookid in(12786,14356,17695,19354)

结果为：

BookID

BookTitle

Copyright

12786

Java

1934

14356

PHP

1966

17695

Pl/SQL

1980

19354

www.java2s.com

1993

这是一个非常简单的mysql in实例，上面的SQL语句其实也可以用or代替：

select *from book where bookid=12786 or bookid=14356 or bookid=17695 or bookid=19354

2.获取book表中bookid字段的值在authorbook表中存在的书本信息：

select *from book where bookid in(select distinct BookID from authorbook)

结果为：

BookID

BookTitle

Copyright

12786

Java

1934

14356

PHP

1966

15729

PERL

1932

16284

Oracle

1996

17695

Pl/SQL

1980

19264

JavaScript

1992

19354

www.java2s.com

1993

因为bookid为13331的书本不在authorbook表中，所以该书本信息不在查询结果集中。

3.获取book表中bookid字段的值不在authorbook表中的书本信息：

select *from book where bookid not in(select distinct BookID from authorbook)

结果为：

BookID

BookTitle

Copyright

13331

MySQL

1919

4.再来一个复杂一点的：

SELECT BookTitle, Copyright

FROM Books

WHERE Copyright NOT IN

(

SELECT b.copyright

FROM Books AS b JOIN AuthorBook AS ab USING (BookID)

JOIN Authors AS a USING (AuthID)

WHERE AuthLN='MySQL'

)

ORDER BY BookTitle

/* http://www.manongjc.com/article/1404.html */

运行结果：

BookID

BookTitle

Copyright

12786

Java

1934

19264

JavaScript

1992

13331

MySQL

1919

16284

Oracle

1996

15729

PERL

1932

14356

PHP

1966

17695

Pl/SQL

1980

19354

www.java2s.com

1993

5. not in 其实相当于<> ALL， 因此在第三个实例中，SQL代码也可以这样写：

select *from book where bookid <> all(select distinct BookID from authorbook)