复合主键与外键多对多关联场景下如何查询指定作者的书籍

你原有查询的问题是没有为三张表添加关联匹配条件，仅筛选了authors_Books.authorId=1，会触发多表笛卡尔积，返回大量不符合预期的重复数据。

符合要求（不使用JOIN语法）的正确SQL如下：

select 
  books.bookTitle, 
  books.price, 
  books.publisher, 
  authors.firstName, 
  authors.lastName 
from 
  books, 
  authors, 
  authors_Books 
where 
  -- 关联作者表和中间关联表
  authors.authorId = authors_Books.authorId
  -- 关联书籍表和中间关联表
  and books.bookId = authors_Books.bookId
  -- 筛选作者ID为1（即Mark Dunn）的记录
  and authors_Books.authorId=1 ;

如果不想硬编码作者ID，也可以通过姓名做筛选：

select 
  books.bookTitle, 
  books.price, 
  books.publisher, 
  authors.firstName, 
  authors.lastName 
from 
  books, 
  authors, 
  authors_Books 
where 
  authors.authorId = authors_Books.authorId
  and books.bookId = authors_Books.bookId
  and authors.firstName = 'Mark'
  and authors.lastName = 'Dunn';

内容的提问来源于stack exchange，提问作者User27854