MySQL INNER JOIN、LEFT JOIN、RIGHT JOIN 和 FULL JOIN 有什么区别？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5706437/
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
What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?
提问by Lion King
What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOINand FULL JOINin MySQL?
什么之间的区别INNER JOIN，LEFT JOIN，RIGHT JOIN并FULL JOIN在MySQL的？
回答by Pranay Rana
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 Brian Leeming
INNER JOINgets all records that are common between both tables based on the supplied ON clause.
INNER JOIN根据提供的 ON 子句获取两个表之间共有的所有记录。
LEFT JOINgets all records from the LEFT linked and the related record from the right table ,but if you have selected some columns from the RIGHT table, if there is no related records, these columns will contain NULL.
LEFT JOIN获取LEFT 链接的所有记录和右表的相关记录，但是如果您从RIGHT 表中选择了一些列，如果没有相关记录，这些列将包含NULL。
RIGHT JOINis like the above but gets all records in the RIGHT table.
RIGHT JOIN与上面类似，但获取 RIGHT 表中的所有记录。
FULL JOINgets all records from both tables and puts NULL in the columns where related records do not exist in the opposite table.
FULL JOIN从两个表中获取所有记录，并将 NULL 放在相对表中不存在相关记录的列中。
回答by Arunprasanth K V
An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.
SQL JOIN 子句用于根据它们之间的公共字段组合来自两个或多个表的行。
There are different types of joins available in SQL:
SQL 中有不同类型的连接可用：
INNER JOIN: returns rows when there is a match in both tables.
INNER JOIN：当两个表都匹配时返回行。
LEFT JOIN: returns all rows from the left table, even if there are no matches in the right table.
LEFT JOIN：返回左表中的所有行，即使右表中没有匹配项。
RIGHT JOIN: returns all rows from the right table, even if there are no matches in the left table.
RIGHT JOIN：返回右表中的所有行，即使左表中没有匹配项。
FULL JOIN: It combines the results of both left and right outer joins.
FULL JOIN：它结合了左右外连接的结果。
The joined table will contain all records from both the tables and fill in NULLs for missing matches on either side.
连接表将包含两个表中的所有记录，并为任一侧缺少的匹配项填充 NULL。
SELF JOIN: is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.
SELF JOIN：用于将一个表与自身连接起来，就好像该表是两个表一样，临时重命名 SQL 语句中的至少一个表。
CARTESIAN JOIN: returns the Cartesian product of the sets of records from the two or more joined tables.
CARTESIAN JOIN：返回来自两个或多个连接表的记录集的笛卡尔积。
WE can take each first four joins in Details :
我们可以在 Details 中获取前四个连接：
We have two tables with the following values.
我们有两个具有以下值的表。
TableA
表A
id firstName lastName
.......................................
1 arun prasanth
2 ann antony
3 sruthy abc
6 new abc
TableB
表B
id2 age Place
................
1 24 kerala
2 24 usa
3 25 ekm
5 24 chennai
....................................................................
………………………………………………………………………………………………………………………………………………………… ………………
INNER JOIN
内部联接
Note:it gives the intersection of the two tables, i.e. rows they have common in TableA and TableB
注意：它给出了两个表的交集，即它们在 TableA 和 TableB 中共有的行
Syntax
句法
SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;
Apply it in our sample table :
将其应用到我们的示例表中：
SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place
FROM TableA
INNER JOIN TableB
ON TableA.id = TableB.id2;
Result Will Be
结果将是
firstName lastName age Place
..............................................
arun prasanth 24 kerala
ann antony 24 usa
sruthy abc 25 ekm
LEFT JOIN
左加入
Note: will give all selected rows in TableA, plus any common selected rows in TableB.
注意：将给出 TableA 中的所有选定行，以及 TableB 中任何常见的选定行。
Syntax
句法
SELECT table1.column1, table2.column2...
FROM table1
LEFT JOIN table2
ON table1.common_field = table2.common_field;
Apply it in our sample table :
将其应用到我们的示例表中：
SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place
FROM TableA
LEFT JOIN TableB
ON TableA.id = TableB.id2;
Result
结果
firstName lastName age Place
...............................................................................
arun prasanth 24 kerala
ann antony 24 usa
sruthy abc 25 ekm
new abc NULL NULL
RIGHT JOIN
右加入
Note: will give all selected rows in TableB, plus any common selected rows in TableA.
注意：将给出 TableB 中的所有选定行，以及 TableA 中任何常见的选定行。
Syntax
句法
SELECT table1.column1, table2.column2...
FROM table1
RIGHT JOIN table2
ON table1.common_field = table2.common_field;
Apply it in our sample table :
将其应用到我们的示例表中：
SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place
FROM TableA
RIGHT JOIN TableB
ON TableA.id = TableB.id2;
Result
结果
firstName lastName age Place
...............................................................................
arun prasanth 24 kerala
ann antony 24 usa
sruthy abc 25 ekm
NULL NULL 24 chennai
FULL JOIN
全面加入
Note:It will return all selected values from both tables.
注意：它将从两个表中返回所有选定的值。
Syntax
句法
SELECT table1.column1, table2.column2...
FROM table1
FULL JOIN table2
ON table1.common_field = table2.common_field;
Apply it in our sample table :
将其应用到我们的示例表中：
SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place
FROM TableA
FULL JOIN TableB
ON TableA.id = TableB.id2;
Result
结果
firstName lastName age Place
...............................................................................
arun prasanth 24 kerala
ann antony 24 usa
sruthy abc 25 ekm
new abc NULL NULL
NULL NULL 24 chennai
Interesting Fact
有趣的事实
For INNER joins the order doesn't matter
对于 INNER 加入，顺序无关紧要
For (LEFT, RIGHT or FULL) OUTER joins,the order matter
对于（LEFT、RIGHT 或 FULL）外部连接，顺序很重要
Better to go check this Linkit will give you interesting details about join order
最好去检查这个链接它会给你关于加入顺序的有趣细节
