MySQL长远浅出易懂之MySQL-left join和 right join的用法

那七个东西简单模糊,明日来讲讲

一、准备工作

率先创制 2 个表 t1 和 t2, 并插入数据

CREATE TABLE `t1` (
  `id1` int(11) NOT NULL AUTO_INCREMENT,
  `num1` int(11) DEFAULT NULL,
  PRIMARY KEY (`id1`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

insert into t1 values(1,10),(2,20),(3,30),(4,40),(5,50);

table t1.png

CREATE TABLE `t2` (
  `id2` int(11) NOT NULL AUTO_INCREMENT,
  `num2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id2`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1

insert into t2 values(1,100),(3,300),(5,500),(7,700),(9,900);

table t2.png

二、left join
官方解释,装逼用的,可以跳过

MySQL implements an A LEFT JOIN B join_condition as follows:

  • Table B is set to depend on table A and all tables on which A
    depends.
  • Table A is set to depend on all tables (except B) that are used in
    the LEFT JOIN condition.
  • If there is a row in A that matches the WHERE clause, but there is
    no row in B that matches the ON condition, an extra B row is
    generated with all columns set to NULL.

在极端输入select * from t1 left join t2 on t1.id1=t2.id2;出口如下

left join.png

也就是说,表t1左联接表t2, 左侧t1是老大,左侧t2是维护者
表t1负有的笔录都会显得出来,
而表t2只会浮现出满意join_condition的口径的记录,即ti.id1=t2.id2,如若存在t1有的记录而t2没有,则体现NULL

三、right join

MySQL,right join只是刚刚反过来而已,左边的表是老大,左侧的是维护者
在巅峰输入select * from t1 right join t2 on t1.id1=t2.id2;输出如下

right join.png

详情请看官方解释

网站地图xml地图