MySQL EXISTS 和 NOT EXISTS 介绍
SELECT ... FROM table WHERE EXISTS (subquery)
该语法可以理解为:将主查询的数据,放到子查询中做条件验证,根据验证结果(TRUE 或 FALSE)来决定主查询的数据结果是否得以保留。
MySQL EXISTS 和 NOT EXISTS 实例
首先我们创建两个表,Books表和AuthorBook表:
创建Books表的mysql代码如下:
CREATE TABLE Books(
BookID SMALLINT NOT NULL PRIMARY KEY,
BookTitle VARCHAR(60) NOT NULL,
Copyright YEAR NOT NULL
)
ENGINE=INNODB;
然后向Books表中插入数据:
INSERT INTO Books VALUES (12786, 'Java',1934),
(13331, 'MySQL',1919),
(14356, 'PHP',1966),
(15729, 'PERL',1932),
(16284, 'Oracle',1996),
(17695, 'Pl/SQL',1980),
(19264, 'JavaScript',1992),
(19354, 'www.manongjc.com',1993);
此时books表的数据如下:
BookID
BookTitle
Copyright
12786
Java
1934
13331
MySQL
1919
14356
PHP
1966
15729
PERL
1932
16284
Oracle
1966
17695
Pl/SQL
1980
19264
JavaScript
1992
19354
www.manongjc.com
1993
然后创建AuthorBook表,创建AuthorBook表的代码如下:
CREATE TABLE AuthorBook(
AuthID SMALLINT NOT NULL,
BookID SMALLINT NOT NULL,
PRIMARY KEY (AuthID, BookID),
FOREIGN KEY (AuthID) REFERENCES Authors (AuthID),
FOREIGN KEY (BookID) REFERENCES Books (BookID)
)
ENGINE=INNODB;
然后向AuthorBook表中插入数据:
INSERT INTO AuthorBook VALUES (1006, 14356),
(1008, 15729),
(1009, 12786),
(1010, 17695),
(1011, 15729),
(1012, 19264),
(1012, 19354),
(1014, 16284);
此时AuthorBook表的数据如下:
AuthID
BookID
1006
14356
1008
15729
1009
12786
1010
17695
1011
15729
1012
19264
1012
19354
1014
16284
现在我们要查询出books表中的数据,但要求 BookID必须在AuthorBook表中存在。查询的SQL 语句如下:
SELECT BookID, BookTitle
FROM Books AS b
WHERE EXISTS
(
SELECT BookID
FROM AuthorBook AS ab
WHERE b.BookID=ab.BookID
)
查询结果集如下表:
BookID
BookTitle
12786
Java
14356
PHP
15729
PERL
16284
Oracle
17695
Pl/SQL
19264
JavaScript
19354
www.manongjc.com
BookID为13331的数据不在查询结果集中,这是因为在AuthorBook表中并不存在bookid为13331的数据,所以这一行数据不会包括在结果集中。
同时,如果我们要查询出books表中的数据,但要求 BookID不在AuthorBook表中,此时我们要使用到not exists,sql语句如下:
SELECT BookID, BookTitle
FROM Books AS b
WHERE NOT EXISTS
(
SELECT BookID
FROM AuthorBook AS ab
WHERE b.BookID=ab.BookID
)
查询结果集如下表:
BookID
BookTitle
13331
MySQL
小结
EXISTS (subquery) 只返回 TRUE 或 FALSE,因此子查询中的 SELECT * 也可以是 SELECT 1 或其他,官方说法是实际执行时会忽略 SELECT 清单,因此没有区别。
EXISTS 子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比,如果担忧效率问题,可进行实际检验以确定是否有效率问题。
EXISTS 子查询往往也可以用条件表达式、其他子查询或者 JOIN 来替代,何种最优需要具体问题具体分析。