SQL之各種join小結(jié)詳細講解
SQL Left Join, Right Join, Inner Join, and Natural Join 各種Join小結(jié)
在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)交: 選擇左右表中關(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)建一個隱含的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 |
+----------+-----------+----------+-----------+---------------+-------+
最后注意一下,下面等號左右兩邊的關(guān)鍵字是等價的:
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é)詳細講解的文章就介紹到這了,更多相關(guān)SQL之各種join小結(jié)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
mysql 中存在null和空時創(chuàng)建唯一索引的方法
據(jù)庫默認值都有null,此時創(chuàng)建唯一索引時要注意了,此時數(shù)據(jù)庫會把空作為多個重復值2014-10-10微信昵稱帶符號導致插入MySQL數(shù)據(jù)庫時出錯的解決方案
Mysql的utf8編碼最多3個字節(jié),而Emoji表情或者某些特殊字符是4個字節(jié),所以會導致帶有表情的昵稱插入數(shù)據(jù)庫時出錯,下面給大家分享下解決方案,需要的朋友參考下吧2016-12-12MySql Group By對多個字段進行分組的實現(xiàn)方法
這篇文章主要介紹了MySql Group By對多個字段進行分組的實現(xiàn)方法,需要的朋友可以參考下2017-09-09使用python連接mysql數(shù)據(jù)庫之pymysql模塊的使用
這篇文章主要介紹了使用python連接mysql數(shù)據(jù)庫之pymysql模塊的使用,本文給大家介紹的非常詳細,具有一定的參考借鑒價值,需要的朋友可以參考下2019-09-09MySQL中的自定義函數(shù)(CREATE FUNCTION)
這篇文章主要介紹了MySQL中的自定義函數(shù)(CREATE FUNCTION),具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-06-06MySQL中(JOIN/ORDER BY)語句的查詢過程及優(yōu)化方法
sql語句性能達不到你的要求,執(zhí)行效率讓你忍無可忍,一般會造成很多影響。那么我們?nèi)绾谓鉀Q這些問題呢,下面由小編來和大家簡單講下2019-05-05