标签（空格分隔）： MySQL

  • 本文介绍了如何使用 MySQL JOINS 语法.
  • MySQL JOINS 用于从多个表中检索记录. 当在SQL语句中连接两个或者多个表时,就执行 MySQL JOIN.
  • 不同类型的 MySQL 连接:
    1. MySQL INNER JOIN (简单连接)
    2. MySQL LEFT OUTER JOIN (LEFT 外连接)
    3. MySQL RIGHT OUTER JOIN (RIGHT 外连接)
    4. MySQL FULL OUTER JOIN (FULL 外连接)
    5. MySQL CROSS JOIN (笛卡尔积)
  • 注意: 本文的实例都依赖于如下的两个表:
    1. Table: Person
    2. +----+-----------+----------+
    3. | Id | FirstName | LastName |
    4. +----+-----------+----------+
    5. |  1 | Zhang     | San      |
    6. |  2 | Li        | Si       |
    7. |  3 | Wang      | Wu       |
    8. +----+-----------+----------+
    9. Table: Address
    10. +--------+------+----------+-----------+
    11. | AddrId | Id   | City     | Province  |
    12. +--------+------+----------+-----------+
    13. |      1 |    1 | Beijing  | Beijing   |
    14. |      2 |    2 | Shanghai | Shanghai  |
    15. |      5 |    5 | Shenzhen | Guangdong |
    16. +--------+------+----------+-----------+

INNER JOIN (简单连接)

  • MySQL INNER JOINS 返回满足连接条件的多个表中的所有行.

  • 句法:

    1. SELECT columns
    2. FROM table1 
    3. INNER JOIN table2
    4. ON table1.column = table2.column;

  • MySQL INNER JOINS 返回 table1 和 table2 阴影区域的记录.

MySQL Joins的用法 - 图1

  • 实例分析

    1. SELECT Person.Id, Person.FirstName, Person.LastName, Address.City, Address.Province
    2. FROM Person
    3. INNER JOIN Address
    4. ON Person.Id = Address.Id;
    5. // 执行结果如下:
    6. +----+-----------+----------+----------+----------+
    7. | Id | FirstName | LastName | City     | Province |
    8. +----+-----------+----------+----------+----------+
    9. |  1 | Zhang     | San      | Beijing  | Beijing  |
    10. |  2 | Li        | Si       | Shanghai | Shanghai |
    11. +----+-----------+----------+----------+----------+

  • MySQL INNER JOIN 实例 返回 Person 和 Address 的所有行,其中在 Person 和 Address 中都有一个匹配的 Id 值.

  • 旧语法: MySQL INNER JOIN 可以使用旧的隐式语法重写.
    1. SELECT Person.Id, Person.FirstName, Person.LastName, Address.City, Address.Province
    2. FROM Person, Address
    3. WHERE Person.Id = Address.Id;

LEFT OUTER JOIN (LEFT 外连接)

  • MySQL LEFT OUTER JOINS 返回在 ON 条件中指定的 LEFT-hand 表中所有行, 并且 只返回 满足连接条件的其他表中的行.

  • 句法:

    1. SELECT columns
    2. FROM table1
    3. LEFT [OUTER] JOIN table2
    4. ON table1.column = table2.column;

  • MySQL LEFT OUTER JOINS 返回 table1 中的所有记录, 并且只返回与 table1 相交的 table2 中的记录.

MySQL Joins的用法 - 图2

  • 实例分析

    1. SELECT Person.Id, Person.FirstName, Person.LastName, Address.City, Address.Province
    2. FROM Person
    3. LEFT OUTER JOIN Address
    4. ON Person.Id = Address.Id;
    5. // 执行结果如下:
    6. +----+-----------+----------+----------+----------+
    7. | Id | FirstName | LastName | City     | Province |
    8. +----+-----------+----------+----------+----------+
    9. |  1 | Zhang     | San      | Beijing  | Beijing  |
    10. |  2 | Li        | Si       | Shanghai | Shanghai |
    11. |  3 | Wang      | Wu       | NULL     | NULL     |
    12. +----+-----------+----------+----------+----------+

  • MySQL LEFT OUTER JOIN 实例 返回 Person 表中的所有行, 并且仅返回 Address 表中联接字段相等的行.

  • 如果 Person 表中的 Id 值不存在于 Address 表中,则 Address 表中的所有字段在结果集中显示为 .

RIGHT OUTER JOIN (RIGHT 外连接)

  • MySQL RIGHT OUTER JOINS 返回在 ON 条件中指定的 RIGHT-hand 表中所有行, 并且 只返回 满足连接条件的其他表中的行.

  • 句法:

    1. SELECT columns
    2. FROM table1
    3. RIGHT [OUTER] JOIN table2
    4. ON table1.column = table2.column;

  • MySQL RIGHT OUTER JOINS 返回 table2 中的所有记录, 并且只返回与 table2 相交的 table1 中的记录.

MySQL Joins的用法 - 图3

  • 实例分析

    1. SELECT Person.Id, Person.FirstName, Person.LastName, Address.City, Address.Province
    2. FROM Person
    3. RIGHT OUTER JOIN Address
    4. ON Person.Id = Address.Id;
    5. // 执行结果如下:
    6. +------+-----------+----------+----------+-----------+
    7. | Id   | FirstName | LastName | City     | Province  |
    8. +------+-----------+----------+----------+-----------+
    9. |    1 | Zhang     | San      | Beijing  | Beijing   |
    10. |    2 | Li        | Si       | Shanghai | Shanghai  |
    11. | NULL | NULL      | NULL     | Shenzhen | Guangdong |
    12. +------+-----------+----------+----------+-----------+

  • MySQL RIGHT OUTER JOIN 实例 返回 Address 表中的所有行, 并且仅返回 Person 表中联接字段相等的行.

  • 如果 Address 表中的 Id 值不存在于 Person 表中,则 Person 表中的所有字段在结果集中显示为 .

FULL OUTER JOIN (FULL 外连接)

  • MySQL FULL OUTER JOINS 得到 两个表 的所有记录. 如果在其他表中没有匹配,在没有匹配的字段结果显示为 .
  • 但是 MySQL 不支持这种用法 , 我们只能用 UNION 来处理.

  • 句法:

    1. SELECT columns
    2. FROM table1
    3. LEFT OUTER JOIN table2
    4. ON table1.columns = table2.columns
    5. UNION
    6. SELECT columns
    7. FROM table1
    8. RIGHT OUTER JOIN table2
    9. ON table1.columns = table2.columns;

  • MySQL FULL OUTER JOINS 返回 table1 和 table2 中的所有记录, 找不到的子段结果显示为 .

MySQL Joins的用法 - 图4

  1. SELECT Person.Id, Person.FirstName, Person.LastName, Address.City, Address.Province
  2. FROM Person
  3. LEFT OUTER JOIN Address
  4. ON Person.Id = Address.Id
  5. UNION
  6. SELECT Person.Id, Person.FirstName, Person.LastName, Address.City, Address.Province
  7. FROM Person
  8. RIGHT OUTER JOIN Address
  9. ON Person.Id = Address.Id;
  10. // 执行结果如下:
  11. +------+-----------+----------+----------+-----------+
  12. | Id   | FirstName | LastName | City     | Province  |
  13. +------+-----------+----------+----------+-----------+
  14. |    1 | Zhang     | San      | Beijing  | Beijing   |
  15. |    2 | Li        | Si       | Shanghai | Shanghai  |
  16. |    3 | Wang      | Wu       | NULL     | NULL      |
  17. | NULL | NULL      | NULL     | Shenzhen | Guangdong |
  18. +------+-----------+----------+----------+-----------+
  • 另一种情况: 如果你想重复出现结果,可以使用 UNION ALL.
    1. SELECT Person.Id, Person.FirstName, Person.LastName, Address.City, Address.Province
    2. FROM Person
    3. LEFT OUTER JOIN Address
    4. ON Person.Id = Address.Id
    5. UNION ALL
    6. SELECT Person.Id, Person.FirstName, Person.LastName, Address.City, Address.Province
    7. FROM Person
    8. RIGHT OUTER JOIN Address
    9. ON Person.Id = Address.Id;
    10. // 执行结果如下:
    11. +------+-----------+----------+----------+-----------+
    12. | Id   | FirstName | LastName | City     | Province  |
    13. +------+-----------+----------+----------+-----------+
    14. |    1 | Zhang     | San      | Beijing  | Beijing   |
    15. |    2 | Li        | Si       | Shanghai | Shanghai  |
    16. |    3 | Wang      | Wu       | NULL     | NULL      |
    17. |    1 | Zhang     | San      | Beijing  | Beijing   |
    18. |    2 | Li        | Si       | Shanghai | Shanghai  |
    19. | NULL | NULL      | NULL     | Shenzhen | Guangdong |
    20. +------+-----------+----------+----------+-----------+

CROSS JOIN (笛卡尔积)

  • CROSS JOINS 得到 两个表 的所有记录 做 N*M 的组合.
  • CROSS JOINS 不支持 ON语法. 但在 MySQL 中, CROSS JOINS 与 ON 语法使用,有容错处理, 相当于 INTER JOIN.

  • 句法:

    1. SELECT columns
    2. FROM table1
    3. CROSS JOIN table2;

  • 实例分析

    1. SELECT Person.Id, Person.FirstName, Person.LastName, Address.City, Address.Province
    2. FROM Person
    3. CROSS JOIN Address;
    4. // 执行结果如下:
    5. +----+-----------+----------+----------+-----------+
    6. | Id | FirstName | LastName | City     | Province  |
    7. +----+-----------+----------+----------+-----------+
    8. |  1 | Zhang     | San      | Beijing  | Beijing   |
    9. |  1 | Zhang     | San      | Shanghai | Shanghai  |
    10. |  1 | Zhang     | San      | Shenzhen | Guangdong |
    11. |  2 | Li        | Si       | Beijing  | Beijing   |
    12. |  2 | Li        | Si       | Shanghai | Shanghai  |
    13. |  2 | Li        | Si       | Shenzhen | Guangdong |
    14. |  3 | Wang      | Wu       | Beijing  | Beijing   |
    15. |  3 | Wang      | Wu       | Shanghai | Shanghai  |
    16. |  3 | Wang      | Wu       | Shenzhen | Guangdong |
    17. +----+-----------+----------+----------+-----------+