SQL Left Join, Right Join, Inner Join, and Natural Join 各种Join小结

Table Person +----------+-----------+----------+ | PersonId | FirstName | LastName | +----------+-----------+----------+ | 1 | Zhang | San | | 2 | Li | Si | | 3 | Wang | Wu | | 4 | Yang | Liu | +----------+-----------+----------+ -- Table Address +-----------+----------+---------------+-------+ | AddressId | PersonId | City | State | +-----------+----------+---------------+-------+ | 1 | 2 | San Francisco | CA | | 2 | 3 | Los Angeles | CA | | 3 | 1 | San Diego | CA | +-----------+----------+---------------+-------+

 

我们下面一个一个的来看：

Left Join： returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.

左交：返回左表的所有行和匹配的右表的行，如果没有匹配上的用NULL.

SELECT * FROM Person LEFT JOIN Address ON Person.PersonId = Address.PersonId;

+----------+-----------+----------+-----------+----------+---------------+-------+
| PersonId | FirstName | LastName | AddressId | PersonId | City          | State |
+----------+-----------+----------+-----------+----------+---------------+-------+
|        2 | Li        | Si       |         1 |        2 | San Francisco | CA    |
|        3 | Wang      | Wu       |         2 |        3 | Los Angeles   | CA    |
|        1 | Zhang     | San      |         3 |        1 | San Diego     | CA    |
|        4 | Yang      | Liu      |      NULL |     NULL | NULL          | NULL  |
+----------+-----------+----------+-----------+----------+---------------+-------+

 

Right Join: returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match.

右交：返回右表的所有行和匹配的左表的行，如果没有匹配上的用NULL.

SELECT * FROM Person RIGHT JOIN Address ON Person.PersonId = Address.PersonId;

+----------+-----------+----------+-----------+----------+---------------+-------+
| PersonId | FirstName | LastName | AddressId | PersonId | City          | State |
+----------+-----------+----------+-----------+----------+---------------+-------+
|        1 | Zhang     | San      |         3 |        1 | San Diego     | CA    |
|        2 | Li        | Si       |         1 |        2 | San Francisco | CA    |
|        3 | Wang      | Wu       |         2 |        3 | Los Angeles   | CA    |
|     NULL | NULL      | NULL     |         4 |        5 | Memphis       | TN    |
+----------+-----------+----------+-----------+----------+---------------+-------+

 

Inner Join: selects all rows from both tables as long as there is a match between the columns in both tables.

内交： 选择左右表中关键字匹配上的行。

SELECT * FROM Person INNER JOIN Address ON Person.PersonId = Address.PersonId;

+----------+-----------+----------+-----------+----------+---------------+-------+
| PersonId | FirstName | LastName | AddressId | PersonId | City          | State |
+----------+-----------+----------+-----------+----------+---------------+-------+
|        1 | Zhang     | San      |         3 |        1 | San Diego     | CA    |
|        2 | Li        | Si       |         1 |        2 | San Francisco | CA    |
|        3 | Wang      | Wu       |         2 |        3 | Los Angeles   | CA    |
+----------+-----------+----------+-----------+----------+---------------+-------+

 

Full Join: returns all rows from the left table (table1) and from the right table (table2), and it combines the result of both LEFT and RIGHT joins.

全交: 返回左表的所有行和右表的所有行，是左交和右交的联合。

注意，由于MySql中没有Full Join命令，所以我们通过把Left Join和Right Join的结果Union起来也是可以的：

SELECT * FROM Person LEFT JOIN Address ON Person.PersonId = Address.PersonId
UNION
SELECT * FROM Person RIGHT JOIN Address ON Person.PersonId = Address.PersonId;

+----------+-----------+----------+-----------+----------+---------------+-------+
| PersonId | FirstName | LastName | AddressId | PersonId | City          | State |
+----------+-----------+----------+-----------+----------+---------------+-------+
|        2 | Li        | Si       |         1 |        2 | San Francisco | CA    |
|        3 | Wang      | Wu       |         2 |        3 | Los Angeles   | CA    |
|        1 | Zhang     | San      |         3 |        1 | San Diego     | CA    |
|        4 | Yang      | Liu      |      NULL |     NULL | NULL          | NULL  |
|     NULL | NULL      | NULL     |         4 |        5 | Memphis       | TN    |
+----------+-----------+----------+-----------+----------+---------------+-------+

 

Natural Join: creates an implicit join clause for you based on the common columns in the two tables being joined. Common columns are columns that have the same name in both tables. A NATURAL JOIN can be an INNER join, a LEFT OUTER join, or a RIGHT OUTER join. The default is INNER join.

自然交： 根据左右两表的相同列创建一个隐含的join操作，相同列就是两表中列名相同的两列。自然交可以是内交，左交或者是右交。默认是内交。

SELECT * FROM Person NATURAL JOIN Address;

+----------+-----------+----------+-----------+---------------+-------+
| PersonId | FirstName | LastName | AddressId | City          | State |
+----------+-----------+----------+-----------+---------------+-------+
|        1 | Zhang     | San      |         3 | San Diego     | CA    |
|        2 | Li        | Si       |         1 | San Francisco | CA    |
|        3 | Wang      | Wu       |         2 | Los Angeles   | CA    |
+----------+-----------+----------+-----------+---------------+-------+

 

最后注意一下，下面等号左右两边的关键字是等价的：

A LEFT JOIN B      =       A LEFT OUTER JOIN B
A RIGHT JOIN B     =       A RIGHT OUTER JOIN B
A FULL JOIN B      =       A FULL OUTER JOIN B
A INNER JOIN B     =       A JOIN B

 

SQL Left Join, Right Join, Inner Join, and Natural Join 各种Join小结

标签：