SQL之各种join小结详细讲解( 二 )

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

SQL之各种join小结详细讲解( 二 )

推荐阅读

坐高铁可以带饮料过安检吗

培养语感、提高语文素养的技巧努力提高语文素养

宝宝听力障碍怎么办发现听力障碍的方法

农村盖厕所的最佳方位

荒野行动PC版游戏画面闪屏

补水面膜一个星期敷几次呢

白带浅绿色是怎么回事白带为什么是绿色的

怎么注销抖音实名认证

知道群号加群怎么加入

揭开详细答案腾讯理财通能随时转入吗?