sql - 这个 SQL 查询有问题吗

Question

我是 SQL 查询的新手，并试图更好地理解它。此 SQL 查询是否对下图有效？我收到的问题是：

“对于“斯蒂芬·金”撰写（或合着）的每本书，检索名称为“中央”的图书馆分馆拥有的书名和副本数

SELECT Title
FROM BOOK
WHERE BookID = 
(
 SELECT BookID
 FROM BOOK_AUTHORS
 WHERE AuthorName = 'Stephen King'
)
AND
BookID = 
(
 SELECT BookID, No_Of_Copies
 FROM BOOK_COPIES
 WHERE BranchID =
 (
  SELECT BranchID
  FROM LIBRARY_BRANCH
  WHERE BranchName = 'Central'
 )
)

标签： sqljoin

Answer 1

请使用以下查询，但正确的方法是使用联接而不是多个子查询

加入

SELECT B.Title
FROM BOOK B
inner join BOOK_AUTHORS BA
on (B.bookid = ba.bookid)
inner join BOOK_COPIES BC
on (B.bookid = BC.bookid)
inner join LIBRARY_BRANCH LB
on (BC.branchid = LB.branchid)
where B.AuthorName = 'Stephen King'
and LB.BranchName = 'Central';

子查询

SELECT Title
FROM BOOK
WHERE BookID in
(
 SELECT BookID
 FROM BOOK_AUTHORS
 WHERE AuthorName = 'Stephen King'
)
AND
BookID in 
(
 SELECT BookID
 FROM BOOK_COPIES
 WHERE BranchID in
 (
  SELECT BranchID
  FROM LIBRARY_BRANCH
  WHERE BranchName = 'Central'
 )
);