MySQL JOIN 三个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3226912/
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
JOIN three tables
提问by Hadonkey Donk
I am trying to retrieve two sets of information (the red and blue portions of the diagram in the one query.
我正在尝试检索两组信息（一个查询中图表的红色和蓝色部分。
I thought I could do it using the sql as stated below but it does not return me anything when TableC has no records. If TableC has no records, I would still want this query to return me the results as indicated by the BLUE area.
我以为我可以使用如下所述的 sql 来完成它，但是当 TableC 没有记录时它不会返回任何内容。如果 TableC 没有记录，我仍然希望此查询返回蓝色区域指示的结果。
SELECT A.* FROM TableA A
JOIN TableB B ON (A.id = B.a_id)
JOIN TableC C ON (A.id = C.a_id)
SELECT A.* FROM TableA A
JOIN TableB B ON (A.id = B.a_id)
JOIN TableC ON (A.id = C.a_id)
Appreciate any pointers to return me the red and/or blue segments. Thanks in advance =]
感谢任何指示将红色和/或蓝色部分归还给我。提前致谢 =]
回答by IordanTanev
try something like this
尝试这样的事情
SELECT A.* FROM TableA A
LEFT OUTER JOIN TableB B ON (A.id = B.a_id)
LEFT OUTER JOIN TableC C ON (A.id = C.a_id)
WHERE B.a_id IS NOT NULL
OR c.a_id IS NOT NULL
回答by Anax
To understand why you don't get any results when TableChas no records, you need to learn a bit about JOIN types in SQL.
要了解为什么在TableC没有记录时没有得到任何结果，您需要了解一些 SQL 中的 JOIN 类型。
Basically, when you issue the query
基本上，当您发出查询时
TableA A JOIN TableB B ON A.id = B.a_id
you're telling the database to look at both TableAand TableBand fetch all pairs of rows which satisfy the join predicate (A.id = B.a_id). Therefore, if TableBis empty, the database can't find and pairof rows with the prementioned criteria.
您告诉数据库查看TableA和TableB并获取满足连接谓词（A.id = B.a_id）的所有行对。因此，如果TableB是空的，数据库不能找到和对与prementioned标准行。
This type of JOIN is called INNER JOINand it is the most common type of join operation used.
这种类型的 JOIN 称为INNER JOIN，它是最常用的连接操作类型。
In your case you want to fetch allrows from TableA X TableBand all relevantrows from TableC, if such rows exist (based on the join predictate "A.id = C.a_id". This is the case for an OUTER JOIN. The two most common types of such join are the LEFT JOIN (which includes all rows from the left table) and the RIGHT JOIN (which includes all rows from the right table).
在您的情况下，您希望从 中获取所有行TableA X TableB和所有相关行TableC，如果这些行存在（基于连接预测“A.id = C.a_id”。这是OUTER JOIN的情况。两种最常见的类型这种连接是LEFT JOIN（包括左表中的所有行）和RIGHT JOIN（包括右表中的所有行）。
In this case, your query should be:
在这种情况下，您的查询应该是：
SELECT A.*
FROM
(TableA A JOIN Table B B ON A.id = B.a_id)
LEFT JOIN TableC C ON A.id = C.a_ID
I would suggest to have a look at the relevant Wikipediapage, if you want to know more about types of joins in SQL
如果您想了解有关 SQL 中连接类型的更多信息，我建议您查看相关的 Wikipedia页面
Edit
编辑
By following the same logic, if you want to have all rows from TableAand only the relevant rows from tables TableBand TableC(if they exist), your query would become:
通过遵循相同的逻辑，如果您希望获得表中的所有行TableA并且仅获得表中的相关行TableB并且TableC（如果它们存在），您的查询将变为：
SELECT A.*
FROM
(TableA A LEFT JOIN Table B B ON A.id = B.a_id)
LEFT JOIN TableC C ON A.id = C.a_ID
