mysql 子查询 exists 和 not exists使用方法和实例

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,
    Book  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, \' \',1992),
                         (19354, \'www.manongjc.com\',1993);

此时books表的数据如下：

然后创建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表的数据如下：

现在我们要查询出books表中的数据，但要求 BookID必须在AuthorBook表中存在。查询的SQL 语句如下：

SELECT BookID, Book 
FROM Books AS b
WHERE EXISTS
    (
       SELECT BookID
       FROM AuthorBook AS ab
       WHERE b.BookID=ab.BookID
    )

查询结果集如下表：

BookID为13331的数据不在查询结果集中，这是因为在AuthorBook表中并不存在bookid为13331的数据，所以这一行数据不会包括在结果集中。

同时，如果我们要查询出books表中的数据，但要求 BookID不在AuthorBook表中，此时我们要使用到not exists，sql语句如下：

SELECT BookID, Book 
FROM Books AS b
WHERE NOT EXISTS
    (
       SELECT BookID
       FROM AuthorBook AS ab
       WHERE b.BookID=ab.BookID
    )

查询结果集如下表：

小结

• EXISTS (subquery) 只返回 TRUE 或 FALSE，因此子查询中的 SELECT * 也可以是 SELECT 1 或其他，官方说法是实际执行时会忽略 SELECT 清单，因此没有区别。
• EXISTS 子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，可进行实际检验以确定是否有效率问题。
• EXISTS 子查询往往也可以用条件表达式、其他子查询或者 JOIN 来替代，何种最优需要具体问题具体分析。