首页 > 数据库技术 > 详细

MySQL中join的7种方法

时间:2020-03-14 12:39:26      阅读:43      评论:0      收藏:0      [点我收藏+]

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

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!