Question

求救啊 关于mysql的问题，in和not exists返回的结果不一样啊

这是表的创建CREATE TABLE Member(
MemNo INT UNSIGNED NOT NULL,
DriverLicState VARCHAR(20),
DriverLicNo VARCHAR(40),
Fname VARCHAR(20),
MI VARCHAR(10),
Lname VARCHAR(20),
Address VARCHAR(250),
PhoneNumber VARCHAR(15),
PRIMARY KEY (MemNo));

CREATE TABLE Book(
Book_ID INT UNSIGNED NOT NULL,
Edition VARCHAR(80),
BorrowerMemNo INT UNSIGNED,
BorrowDueDate DATE,
CallNumber VARCHAR(40),
LibCheck INT UNSIGNED,
PRIMARY KEY (Book_ID),
FOREIGN KEY (CallNumber) REFERENCES Title(CallNumber),
FOREIGN KEY (BorrowerMemNo) REFERENCES Member(MemNo),
FOREIGN KEY (LibCheck) REFERENCES Librarian(SSN));

CREATE TABLE Author(
CallNumber VARCHAR(40) NOT NULL,
Fname VARCHAR(40) NOT NULL,
MI VARCHAR(10),
Lname VARCHAR(40) NOT NULL,
PRIMARY KEY (CallNumber, Fname, Lname),
FOREIGN KEY (CallNumber) REFERENCES Title(CallNumber));

这是我的两个查询：
SELECT
Member.MemNo,
Member.Fname,
Member.Lname
FROM
Member
WHERE
Member.MemNo IN (
SELECT
Book.BorrowerMemNo
FROM
Book
WHERE
Book.CallNumber IN (
SELECT
Author.CallNumber
FROM
Author
WHERE
Author.Lname = 'Collins'
)
)

SELECT
memno,
fname,
lname
FROM
member
WHERE
NOT EXISTS (
SELECT
*
FROM
author
WHERE
author.lname = 'Collins'
AND NOT EXISTS (
SELECT
*
FROM
book
WHERE
member.memno = book.borrowermemno
AND author.callnumber = book.callnumber
)
);

Answer 1

第一个是查询借过作者为Collins的书的所有人，

第二个是查询所有没借过作者非Collins的书的所有人，
当然会不一样，
如果有人即结果作者为Collins的书也借过作者非Collins的书，
那么第一个能查询到，第二个查不到。

追问

我又把数据看了一下，发现第二个查到的人既借了Collins的书也借了其他人的书啊，这是怎么回事啊

还在吗