SQL Left Join, Right Join, Inner Join, and Natural Join 各種Join小結(jié)

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

上面這張圖很好的闡釋了Left Join， Right Join， Inner Join，和Full Outer Join的區(qū)別，下面用我們用一個(gè)簡(jiǎn)單的例子來幫助我們理解和區(qū)分，現(xiàn)在有兩個(gè)表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    |
+-----------+----------+---------------+-------+

我們下面一個(gè)一個(gè)的來看：

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)交： 選擇左右表中關(guān)鍵字匹配上的行。

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的結(jié)果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)建一個(gè)隱含的join操作，相同列就是兩表中列名相同的兩列。自然交可以是內(nèi)交，左交或者是右交。默認(rèn)是內(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    |
+----------+-----------+----------+-----------+---------------+-------+

最后注意一下，下面等號(hào)左右兩邊的關(guān)鍵字是等價(jià)的：

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

到此這篇關(guān)于SQL之各種join小結(jié)詳細(xì)講解的文章就介紹到這了,更多相關(guān)SQL之各種join小結(jié)內(nèi)容請(qǐng)搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站！

香港服務(wù)器租用

版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。