数据库

MySQL中join的7种方法

云博小周宇3 Mins Read

join查询的7中方法

  • 手写顺序：

    SELECT DISTINCT
  <select_list>
FROM
  <left_table> <join_type>
JOIN <right_table> ON <join_condition>
WHERE
  <where_condition>
GROUP BY
  <group_by_list>
HAVING
  <having_condition>
ORDER BY
  <order_by_condition>
LIMIT <limit_number>

  • MySQL执行顺序

    FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>

  • join连接方式：

    • 内连接：

    SELECT <select_list> FROM TableA A INNER JOIN TableB B ON A.Key = B.Key

    
- 左连接：

![](https://img2020.cnblogs.com/blog/1644171/202003/1644171-20200314113234405-815784414.png)

```mysql
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key

    • 右连接：

    SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key

    
- 只有A

![](https://img2020.cnblogs.com/blog/1644171/202003/1644171-20200314113312810-147549149.png)

```mysql
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL

  • 只有B

      SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL

    • 全连接

      # MySQL没有FULL OUTER语法。
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key
union
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key;

    • A，B各自独有：

      ?

    # MySQL没有FULL OUTER语法。
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL
union
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL;