SQL Left Join, Right Join, Inner Join, and Natural Join 各種Join小結

在SQL語言中，存在著各種Join，有Left Join, Right Join, Inner Join, and Natural Join等，對于初學者來說肯定一頭霧水，都是神馬跟神馬啊，它們之間到底有著怎樣的區(qū)別和聯(lián)系呢，我們先用一張圖片來說明：

上面這張圖很好的闡釋了Left Join， Right Join， Inner Join，和Full Outer Join的區(qū)別，下面用我們用一個簡單的例子來幫助我們理解和區(qū)分，現(xiàn)在有兩個表Person和Address:

-- 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.

內(nèi)交： 選擇左右表中關鍵字匹配上的行。

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.

全交: 返回左表的所有行和右表的所有行，是左交和右交的聯(lián)合。

注意，由于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.

自然交： 根據(jù)左右兩表的相同列創(chuàng)建一個隱含的join操作，相同列就是兩表中列名相同的兩列。自然交可以是內(nèi)交，左交或者是右交。默認是內(nèi)交。

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之各種join小結詳細講解的文章就介紹到這了,更多相關SQL之各種join小結內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論