Mysql join 连接的7种情况

目录

建表

内连接

左外连接

右外连接

左连接

右连接

全连接

两表中没有出现共同的数据

---

建表

建立简单的学生表和教师表，使用这两个边来演示 join 连接的 7 中情况。

CREATE TABLE `student` (    // 学生表
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(8) NOT NULL,
  `age` int(11) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;


CREATE TABLE `teacher` (    // 教师表
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(8) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

       

内连接

 

SELECT * from student s inner join teacher t on s.teacher_id=t.id;

左外连接

SELECT * from student s LEFT join teacher t on s.teacher_id=t.id order by s.id;

右外连接

SELECT * from student s right join teacher t on s.teacher_id=t.id order by s.id;

左连接

SELECT * from student s left join teacher t on s.teacher_id=t.id where t.id is null order by s.id;

右连接

SELECT * from student s right join teacher t on s.teacher_id=t.id where s.id is null order by s.id;

全连接

SELECT * from student s left JOIN teacher t on s.teacher_id=t.id 
union SELECT * from student s right JOIN teacher t on s.teacher_id=t.id;

两表中没有出现共同的数据

SELECT * from student s left JOIN teacher t on s.teacher_id=t.id where t.id is null 
union SELECT * from student s right JOIN teacher t on s.teacher_id=t.id where s.id is null;