Fork me on GitHub

MySQL中join的7种方法

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;
posted @   是阿凯啊  阅读(5958)  评论(0编辑  收藏  举报
编辑推荐:
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· NetPad:一个.NET开源、跨平台的C#编辑器
· PowerShell开发游戏 · 打蜜蜂
· 凌晨三点救火实录:Java内存泄漏的七个神坑,你至少踩过三个!
点击右上角即可分享
微信分享提示