有没有更惯用的方法来合并两个表中的相关行？

Question

我使用一个人为的例子来说明这个问题。

想象一个简单的书籍表，其中包含标题和主题/类型。此外，还有一个相关主题的关联表。

> SELECT * FROM books;
+----+--------+-----------+
| id | title  | subject   |
+----+--------+-----------+
|  1 | Book A | science   |
|  2 | Book B | reference |
|  3 | Book C | fiction   |
+----+--------+-----------+

> SELECT * FROM related_subjects;
+----+---------+---------+
| id | book_id | subject |
+----+---------+---------+
|  1 |       1 | physics |
|  2 |       1 | space   |
|  3 |       3 | crime   |
+----+---------+---------+

我想要一个可以输出所有标题+主题组合的查询，这样它看起来像：

+----+--------+-----------+
| id | title  | SUBJECT   |
+----+--------+-----------+
|  1 | Book A | science   |
|  1 | Book A | space     |
|  1 | Book A | physics   |
|  2 | Book B | reference |
|  3 | Book C | fiction   |
|  3 | Book C | crime     |
+----+--------+-----------+

最明显的方法，是使用 UNION 如下：

SELECT books.id, books.title, SUBJECT FROM books
UNION
SELECT books.id, books.title, related_subjects.subject FROM books
INNER JOIN related_subjects ON related_subjects.book_id = books.id;

这会产生一个很好的结果：

+----+--------+-----------+
| id | title  | SUBJECT   |
+----+--------+-----------+
|  1 | Book A | science   |
|  2 | Book B | reference |
|  3 | Book C | fiction   |
|  1 | Book A | space     |
|  1 | Book A | physics   |
|  3 | Book C | crime     |
+----+--------+-----------+

但是，如果自然输出顺序与我想要的输出类似，那么最好是先出现 books 行，然后是 related_subjects 表中的相关行，依此类推。

我很好奇是否有更好/更有效的方法来完成此类任务？特别是它会给我一个更自然的排序，而不必先对最终结果应用排序。

注意：当然，我知道我可以通过对 books.id、related_subjects.id 进行排序来将数据库排序应用于联合输出，但我的现实世界应用程序中的输出包含数十万行，因此避免如果可以避免的话，这是一种相对昂贵的类型。

Answer 1

在联合查询中引入计算列进行排序：

SELECT id, title, subject
FROM
(
    SELECT id, title, subject, 1 AS src FROM books
    UNION ALL
    SELECT b.id, b.title, rs.subject, 2
    FROM books b
    INNER JOIN related_subjects rs ON rs.book_id = b.id
) t
ORDER BY id, src;

Answer 2

书籍和主题之间似乎存在一对多的关系。因此，您可以从 books 表中删除主题列，并确保适用于该图书的所有主题都在 lated_subjects 表中。那么您不必使用 UNION 来获取两者，您只需从 book 到 related_subjects 进行连接即可。

理论上，除非您指定 ORDER BY，否则 SQL 不保证查询结果的任何顺序。但实际上，InnoDB 按照在用于查找行的索引中读取行的顺序返回行。