具有唯一结果行的 MYSQL JOIN
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5713326/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MYSQL JOIN with unique result rows
提问by tgriesser
Say I have 2 tables, one called categories and one called cat_pages.
假设我有 2 个表，一个称为类别，另一个称为 cat_pages。
The categories table has columns ID, title and timestamp. For example:
类别表包含列 ID、标题和时间戳。例如：
CREATE TABLE categories (
id INT UNSIGNED PRIMARY KEY,
title VARCHAR(32),
`timestamp` TIMESTAMP,
INDEX (title)
) Engine=InnoDB;
The cat_pages has 2 columns, cat_id and page_id:
cat_pages 有 2 列，cat_id 和 page_id：
CREATE TABLE cat_pages (
cat_id INT UNSIGNED
REFERENCES categories (id)
ON DELETE CASCADE ON UPDATE CASCADE,
page_id INT UNSIGNED
REFERENCES pages (id)
ON DELETE CASCADE ON UPDATE CASCADE,
UNIQUE INDEX (cat_id, page_id),
INDEX (page_id, cat_id),
) Engine=InnoDB;
I'm trying to join the categories table with the cat_pages table on the ID, such that
我正在尝试将类别表与 ID 上的 cat_pages 表连接起来，这样
- Only categories with id's in the category_pages table are retrieved and
- Each category is only displayed once in the resultset
- 仅检索 category_pages 表中带有 id 的类别，并且
- 每个类别在结果集中只显示一次
The query:
查询：
SELECT * FROM categories as c
LEFT JOIN cat_pages as p ON c.id = p.cat_id
produces a result set that has the categories repeated multiple times (as there are multiple matches in the cat_pages table. What do I need so that each category is only shown once, and not at all if there are no matches in the cat_pages table?
生成一个结果集，其中的类别重复多次（因为 cat_pages 表中有多个匹配项。我需要什么才能使每个类别只显示一次，如果 cat_pages 表中没有匹配项，则根本不显示？
回答by outis
If you don't want categories that aren't in cat_pages, don't use a left join; use an inner join. A left join includes every row from the left table, even if there isn't a matching row in the right table (the missing fields are given NULL values). A right join is similar, but includes all rows from the right table. An outer join includes all rows from the left and right tables, joining rows that have matches and joining rows without matches with NULL values. An inner join, by contrast, only includes matching rows. To put it another way, the intersection of left and right joins is an inner join; their union is an outer join. Jeff Atwood posted some nice Venn diagrams describing joins, though it should be noted that the sets in the circles aren't properly the left and right tables, but rather the results of the left and right joins of the left and right tables.
如果您不想要不在 中的类别cat_pages，请不要使用左连接；使用内连接。左联接包括左表中的每一行，即使右表中没有匹配的行（缺失的字段被赋予 NULL 值）。右联接与此类似，但包括右表中的所有行。外部联接包括左表和右表中的所有行，联接具有匹配项的行和联接不具有 NULL 值的匹配行。相比之下，内部联接仅包括匹配的行。换句话说，左右连接的交集是内连接；他们的联合是一个外连接。Jeff Atwood 发布了一些描述连接的不错的维恩图，但需要注意的是，圆圈中的集合并不是左右表，而是左右表的左右连接的结果。
To get distinct rows, you can use a DISTINCTmodifier:
要获得不同的行，您可以使用DISTINCT修饰符：
SELECT DISTINCT c.*
FROM categories AS c
INNER JOIN cat_pages AS cp ON c.id = cp.cat_id
As for SELECT * ..., see "What is the reason not to use select *?"
至于SELECT * ...，请参阅“不使用 select * 的原因是什么？”
Another approach to getting distinct rows would be to use an EXISTSclause or INoperator, but the join is likely more performant (though only an EXPLAINwould tell you for certain). Just make sure you have appropriate indices set.
获取不同行的另一种方法是使用EXISTS子句或IN运算符，但连接的性能可能更高（尽管只有 anEXPLAIN会告诉您确定）。只要确保您设置了适当的索引即可。
回答by KebdnK
Why don't you use an Inner Join?
为什么不使用内部联接？
SELECT * FROM categories as c INNER JOIN cat_pages as p ON c.id = p.cat_id
Or
或者
SELECT * FROM categories as c LEFT JOIN cat_pages as p ON c.id = p.cat_id WHERE p.cat_id IS NOT NULL
Left Join selects all on the left table and the matches on the right table.
左联接选择左表中的所有内容和右表中的匹配项。
