SQL语句inner join / left join / right join使用
A INNER JOIN B 内边接 获取AB两个表中字段匹配关系的记录。
A LEFT JOIN
B 左连接 以左表A为主，先查询出左表A，按照ON后的关联条件匹配右表B，右表B中没有匹配到的用NULL填充。
A RIGHT
JOIN B 右连接 以右表B为主，先查询出右表B，按照ON后的关联条件匹配左表A，左表A中没有匹配到的用NULL填充。
一、创建测试环境
# 创建学生表1 DROP TABLE IF EXISTS stu1; CREATE TABLE stu1( id int(11) NOT NULL AUTO_INCREMENT COMMENT '主键', name varchar(30) DEFAULT NULL COMMENT '姓名', class varchar(10) DEFAULT NULL COMMENT '班级', tel varchar(30) DEFAULT NULL COMMENT '电话', addr varchar(400) DEFAULT NULL COMMENT '地址', primary key(id), key(name), key(tel) )ENGINE=InnoDB DEFAULT CHARSET=UTF8 COMMENT='学生表1'; # 向stu1中插入测试数据 insert into stu1(name,class,tel,addr) values ('yiyi','cls1','13011111111','北京'), ('zhang3','cls1','13000000033','海南三亚'), ('zhang3','cls1','13111111133','广西桂林'), ('li4','cls1','13044444444','四川成都'), ('wang5','cls1','13000000005','河南郑州'); # 创建学生表2 DROP TABLE IF EXISTS stu2; CREATE TABLE stu2( id int(11) NOT NULL AUTO_INCREMENT COMMENT '主键', name varchar(30) DEFAULT NULL COMMENT '姓名', class varchar(10) DEFAULT NULL COMMENT '班级', tel varchar(30) DEFAULT NULL COMMENT '电话', addr varchar(400) DEFAULT NULL COMMENT '地址', primary key(id), key(name), key(tel) )ENGINE=InnoDB DEFAULT CHARSET=UTF8 COMMENT='学生表2'; # 向stu2中插入测试数据 insert into stu2(name,class,tel,addr) values ('xiao2','cls2','13022222222','山东烟台'), ('li4','cls2','13044444444','四川成都'), ('wang5','cls2','13000000005','河南郑州'), ('wang5','cls2','13011111155','吉林长春');
二、查看测试数据
mysql> select * from stu1; +----+--------+-------+-------------+--------------+ | id | name | class | tel | addr | +----+--------+-------+-------------+--------------+ | 1 | yiyi | cls1 | 13011111111 | 北京 | | 2 | zhang3 | cls1 | 13000000033 | 海南三亚 | | 3 | zhang3 | cls1 | 13111111133 | 广西桂林 | | 4 | li4 | cls1 | 13044444444 | 四川成都 | | 5 | wang5 | cls1 | 13000000005 | 河南郑州 | +----+--------+-------+-------------+--------------+ 5 rows in set (0.00 sec) mysql> select * from stu2; +----+-------+-------+-------------+--------------+ | id | name | class | tel | addr | +----+-------+-------+-------------+--------------+ | 1 | xiao2 | cls2 | 13022222222 | 山东烟台 | | 2 | li4 | cls2 | 13044444444 | 四川成都 | | 3 | wang5 | cls2 | 13000000005 | 河南郑州 | | 4 | wang5 | cls2 | 13011111155 | 吉林长春 | +----+-------+-------+-------------+--------------+ 4 rows in set (0.00 sec)
三、查询
3.1 INNER JOIN
示例1：查询stu1和stu2中name值相同的行 mysql> select a.name,a.class, b.name,b.class from stu1 a, stu2 b where a.name=b.name; +-------+-------+-------+-------+ | name | class | name | class | +-------+-------+-------+-------+ | li4 | cls1 | li4 | cls2 | | wang5 | cls1 | wang5 | cls2 | | wang5 | cls1 | wang5 | cls2 | +-------+-------+-------+-------+ 3 rows in set (0.00 sec) 缺点：在上面语句中，实际上是创建了两张表的笛卡尔积，所有可能的组合都会被创建出来。 在笛卡尔连接中，在上面的例子中，如果有1000stu1记录和1000条stu2记录，这个查询会先产生1000000个结果，然后通过正确的name过滤出1000条记录。 这是一种低效利用数据库资源，数据库多做100倍的工作。 在大型数据库中，笛卡尔连接是一个大问题，对两个大表的笛卡尔积会创建数10亿或万亿的记录。 示例2：为了避免创建笛卡尔积，应该使用 INNER JOIN ： mysql> SELECT a.name,a.class,b.name,b.class FROM stu1 a INNER JOIN stu2 b ON a.name=b.name; +-------+-------+-------+-------+ | name | class | name | class | +-------+-------+-------+-------+ | li4 | cls1 | li4 | cls2 | | wang5 | cls1 | wang5 | cls2 | | wang5 | cls1 | wang5 | cls2 | +-------+-------+-------+-------+ 3 rows in set (0.00 sec)
3.2 LEFT JOIN
示例3：左连接 使用左连接，查询stu1表，并用stu1.name关联stu2.name，匹配stu2对应记录，如果stu2没有就用NULL值填充。 mysql> SELECT a.name,a.class,b.name,b.class FROM stu1 a LEFT JOIN stu2 b ON a.name=b.name ORDER BY a.id; +--------+-------+-------+-------+ | name | class | name | class | +--------+-------+-------+-------+ | yiyi | cls1 | NULL | NULL | | zhang3 | cls1 | NULL | NULL | | zhang3 | cls1 | NULL | NULL | | li4 | cls1 | li4 | cls2 | | wang5 | cls1 | wang5 | cls2 | | wang5 | cls1 | wang5 | cls2 | +--------+-------+-------+-------+ 6 rows in set (0.00 sec)
3.2 RIGHT JOIN
示例4：右连接 使用右连接，查询stu2表，并用stu1.name关联stu2.name，匹配stu1对应记录，如果stu1没有就用NULL值填充。 mysql> SELECT a.name,a.class,b.name,b.class FROM stu1 a RIGHT JOIN stu2 b ON a.name=b.name ORDER BY a.id; +-------+-------+-------+-------+ | name | class | name | class | +-------+-------+-------+-------+ | NULL | NULL | xiao2 | cls2 | | li4 | cls1 | li4 | cls2 | | wang5 | cls1 | wang5 | cls2 | | wang5 | cls1 | wang5 | cls2 | +-------+-------+-------+-------+ 4 rows in set (0.00 sec)
示例5：本示例 与 "示例3"异区同工。 mysql> SELECT a.name,a.class,b.name,b.class FROM stu2 b RIGHT JOIN stu1 a ON a.name=b.name ORDER BY a.id; +--------+-------+-------+-------+ | name | class | name | class | +--------+-------+-------+-------+ | yiyi | cls1 | NULL | NULL | | zhang3 | cls1 | NULL | NULL | | zhang3 | cls1 | NULL | NULL | | li4 | cls1 | li4 | cls2 | | wang5 | cls1 | wang5 | cls2 | | wang5 | cls1 | wang5 | cls2 | +--------+-------+-------+-------+ 6 rows in set (0.01 sec)
