MySQL中join的7种方法 -清零世界

bubuko 2022/1/25 20:05:48 mysql 字数 3164 阅读 873 0 来源 http://www.bubuko.com/infolist-5-1.html

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

左连接：

技术分享图片

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

技术分享图片

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;

MySQL中join的7种方法

原文：https://www.cnblogs.com/xujunkai/p/12491195.html

如果您也喜欢它,动动您的小指点个赞吧

MySQL中join的7种方法

join查询的7中方法

分类汇总

您的鼓励是对我最大的支持