MySQL 我如何决定何时使用右联接/左联接或内联接，或者如何确定哪个表在哪一边？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3308122/
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
How do I decide when to use right joins/left joins or inner joins Or how to determine which table is on which side?
提问by Pranay Rana
I know the usage of joins, but sometimes I come across such a situation when I am not able to decide which joinwill be suitable, a left or right.
我知道连接的用法，但有时我会遇到这样的情况，当我无法决定哪个连接合适，left 或 right。
Here is the query where I am stuck.
这是我被卡住的查询。
SELECT count(ImageId) as [IndividualRemaining],
userMaster.empName AS ID#,
CONVERT(DATETIME, folderDetails.folderName, 101) AS FolderDate,
batchDetails.batchName AS Batch#,
Client=@ClientName,
TotalInloaded = IsNull(@TotalInloaded,0),
PendingUnassigned = @PendingUnassigned,
InloadedAssigned = IsNull(@TotalAssigned,0),
TotalProcessed = @TotalProcessed,
Remaining = @Remaining
FROM
batchDetails
Left JOIN folderDetails ON batchDetails.folderId = folderDetails.folderId
Left JOIN imageDetails ON batchDetails.batchId = imageDetails.batchId
Left JOIN userMaster ON imageDetails.assignedToUser = userMaster.userId
WHERE folderDetails.ClientId =@ClientID and verifyflag='n'
and folderDetails.FolderName IN (SELECT convert(VARCHAR,Value) FROM dbo.Split(@Output,','))
and userMaster.empName <> 'unused'
GROUP BY userMaster.empName, folderDetails.folderName, batchDetails.batchName
Order BY folderDetails.Foldername asc
回答by Pranay Rana
Yes, it depends on the situation you are in.
是的，这取决于您所处的情况。
Why use SQL JOIN?
为什么要使用 SQL JOIN？
Answer: Use the SQL JOIN whenever multiple tables must be accessed through an SQL SELECT statement and no results should be returned if there is not a match between the JOINed tables.
回答：每当必须通过 SQL SELECT 语句访问多个表时，请使用 SQL JOIN，如果 JOIN 表之间不匹配，则不应返回任何结果。
Reading this original article on The Code Projectwill help you a lot: Visual Representation of SQL Joins.
阅读有关The Code Project 的这篇原创文章会对您有很大帮助：SQL Joins 的可视化表示。
Also check this post: SQL SERVER – Better Performance – LEFT JOIN or NOT IN?.
另请查看这篇文章：SQL SERVER – Better Performance – LEFT JOIN or NOT IN？.
Find original one at: Difference between JOIN and OUTER JOIN in MySQL.
回答by WonderWorker
In two sets:
分两组：
Use a full outer join when you want all the results from both sets.
Use an inner join when you want only the results that appear in both sets.
Use a left outer join when you want all the results from set a, but if set b has data relevant to some of set a's records, then you also want to use that data in the same query too.
当您想要来自两个集合的所有结果时，请使用完全外部联接。
当您只想要同时出现在两个集合中的结果时，请使用内部联接。
当您想要来自集合 a 的所有结果时使用左外连接，但如果集合 b 具有与集合 a 的某些记录相关的数据，那么您也希望在同一查询中使用该数据。
Please refer to the following image:
请参考下图：
回答by AcidRaZor
I think what you're looking for is to do a LEFT JOINstarting from the main-table to return all records from the main table regardless if they have valid data in the joined ones (as indicated by the top left 2 circles in the graphic)
我认为您正在寻找的是LEFT JOIN从主表开始，以从主表返回所有记录，无论它们是否在连接的数据中具有有效数据（如图中左上角的 2 个圆圈所示）
JOIN's happen in succession, so if you have 4 tables to join, and you always want all the records from your main table, you need to continue LEFT JOINthroughout, for example:
JOIN 连续发生，所以如果你有 4 个表要加入，并且你总是想要主表中的所有记录，你需要继续LEFT JOIN，例如：
SELECT * FROM main_table
LEFT JOIN sub_table ON main_table.ID = sub_table.main_table_ID
LEFT JOIN sub_sub_table on main_table.ID = sub_sub_table.main_table_ID
If you INNER JOINthe sub_sub_table, it will immediately shrink your result set down even if you did a LEFT JOINon the sub_table.
如果你INNER JOIN使用 sub_sub_table，它会立即缩小你的结果集，即使你在 sub_tableLEFT JOIN上做了一个。
Remember, when doing LEFT JOIN, you need to account for NULLvalues being returned. Because if no record can be joined with the main_table, a LEFT JOINforces that field to appear regardless and will contain a NULL. INNER JOINwill obviously just "throw away" the row instead because there's no valid link between the two (no corresponding record based on the ID's you've joined)
请记住，在执行 时LEFT JOIN，您需要考虑NULL返回的值。因为如果没有记录可以与 main_table 连接，则LEFT JOIN强制该字段出现，并且将包含 NULL。INNER JOIN显然只会“扔掉”该行，因为两者之间没有有效的链接（根据您加入的 ID 没有相应的记录）
However, you mention you have a where statement that filters out the rows you're looking for, so your question on the JOIN's are null & void because that is not your real problem. (This is if I understand your comments correctly)
但是，您提到您有一个 where 语句可以过滤掉您要查找的行，因此您对 JOIN 的问题是无效的，因为这不是您真正的问题。（这是如果我正确理解您的评论）
