MySQL连接：内连接、左外连接、右外连接

末蓝、 2022-10-12 04:13 254阅读 0赞

## 内连接（inner join） ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZUNoYW55_size_16_color_FFFFFF_t_70]

语句：
    	select [表字段1],[表字段2],... from [表A],[表B] where [表A.相同字段] = [表B.相同字段]

隐式内连接：
    select first_name, last_name, order_date, order_amount
    from customers, orders 
    where customers.customer_id = orders.customer_id

显示内连接：
    select first_name, last_name, order_date, order_amount
    from customers c
    inner join orders o
    on c.customer_id = o.customer_id

## 左外连接（left outer join，outer可省略） ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZUNoYW55_size_16_color_FFFFFF_t_70 1]

语句：
    	select [表字段1],[表字段2],... from [表A] LEFT JOIN [表B] on [表A.预关联的字段] = [表B.预关联的字段]

select first_name, last_name, order_date, order_amount
    from customers c
    left join orders o
    on c.customer_id = o.customer_id

SELECT
      o.`id`,
      o.`oil_name`,
      o.`address`,
      o.`longitude`,
      o.`latitude`,
      o.`discount`,
      o.`explains`,
      DATE_FORMAT (o.`adddate`, '%Y-%m-%d %H:%i') ADDDATE,
      o.`status`,
      a.id aid,
      a.`attMiniUrl`,
      a.`attType`,
      a.`attUrl`,
      a.`businessType`,
      a.`isdefault`,
      a.`name`,
      DATE_FORMAT (
        a.`uploadTime`,
        '%Y-%m-%d %h:%i'
      ) uploadTime
    FROM
      b_oil o
      LEFT JOIN b_attachment a
        ON o.id = a.businessId
        AND a.businessType = 1 
    <where>
        o.isdel=0
           <if test="oil_name !=null and oil_name !='' ">
              and o.oil_name like concat('%',#{oil_name},'%')
           </if>
           <if test="address !=null and address !='' ">
              and o.address like concat('%',#{address},'%')
           </if>
           <if test="status!=null and status!=''">
              and o.status=#{status}
           </if>
    </where>
        <if test="begin!=null">
           limit #{begin},#{pageSize}
        </if>

## 右外连接（right outer join，outer可省略 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZUNoYW55_size_16_color_FFFFFF_t_70 2]

select first_name, last_name, order_date, order_amount
    from customers c
    right join orders o
    on c.customer_id = o.customer_id

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZUNoYW55_size_16_color_FFFFFF_t_70]: /images/20221005/bf136999cafa49c79bc5a12db2c37dcc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZUNoYW55_size_16_color_FFFFFF_t_70 1]: /images/20221005/5e07045d1ab44d7aa1de8d305f789d19.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZUNoYW55_size_16_color_FFFFFF_t_70 2]: /images/20221005/164ea073f6ad465c88127892009ed1b6.png