我有三张桌子：
1） 书
Column Type | book_id | isbn | title | year |
book_id integer Auto Increment PK | 1 | 1234 | Book1 | 1999 |
isbn character varying(30) UNIQUE | 2 | 1235 | Book2 | 1999 |
title character varying(150) | 3 | 1236 | Book3 | 2009 |
year smallint
2） 作者
Column Type | author_id | name |
author_id integer Auto Increment PK | 5 | Joe Doe |
name character varying(100) | 7 | John Curry |
| 9 | Jim Webb |
| 11 | Carl Lee |
3） book_author
Column Type | book_id | author_id |
book_id integer REFERENCES book(book_id) | 1 | 5 |
author_id integer REFERENCES author(author_id) | 1 | 7 |
PRIMARY KEY (book_id, author_id) | 2 | 9 |
| 3 | 11 |
一本书可以有一个或多个作者，所以我决定创建book_author表，将book和author表连接在一起。我想创建一个查询来选择例如:1999年出版的所有书籍的书名和作者姓名。该查询应该返回类似以下的内容:
title name
Book1 Joe Doe
Book1 John Curry
Book2 Jim Webb
我已经设法创建了两个以某种方式实现此目的的查询（嗯不完全是）。
SELECT name from author WHERE author_id =
ANY (SELECT author_id FROM book_author WHERE book_author.book_id = 1)
SELECT title FROM book WHERE book_id = ANY
(SELECT book_id FROM book_author WHERE author_id = 5 OR author_id = 7)
你能为我提供一个单一的查询，达到上述目的。如果你能解释一下这个查询的结构，以及它背后的原因，那就太好了。先谢谢你了
您可以读出表之间的连接
Select b.title, a.name from book_author ba
join author a on (a.author_id =ba.author_id)
join book b on (ba.book_id = b.book_id)
where b.year=1999
查询结果 ：
title name
Book1 Joe Doe
Book1 John Curry
Book2 Jim Webb
加入表，您可以选择图书名称，作者选择book_author这是包含事务数据的源表，并加入查找表（图书和作者）以获取有关这些事务的信息。
Select b.title,a.name from book_author as ba
inner join author as a on ba.author_id=a.author_id
Inner join book as b on b.book_id=ba.book_id
