Mysql左连接left join on与右连接 right join on,内连接union区别

数据库中建立了两张表：
tb_stu表：

DROP TABLE IF EXISTS `tb_stu`;CREATE TABLE `tb_stu` (  `id` int(10) NOT NULL AUTO_INCREMENT,  `name` varchar(15) DEFAULT NULL,  `sex` varchar(5) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;-- ------------------------------ Records of tb_stu-- ----------------------------INSERT INTO `tb_stu` VALUES ('1', 'a', '男');INSERT INTO `tb_stu` VALUES ('2', 'b', '女');INSERT INTO `tb_stu` VALUES ('3', 'c', '男');INSERT INTO `tb_stu` VALUES ('4', 'd', '男');INSERT INTO `tb_stu` VALUES ('5', 'e', '女');

tb_school表：

DROP TABLE IF EXISTS `tb_school`;CREATE TABLE `tb_school` (  `id` int(10) NOT NULL AUTO_INCREMENT,  `name` varchar(15) DEFAULT NULL,  `school` varchar(15) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;-- ------------------------------ Records of tb_school-- ----------------------------INSERT INTO `tb_school` VALUES ('1', '1', '1');INSERT INTO `tb_school` VALUES ('2', '2', '2');INSERT INTO `tb_school` VALUES ('3', '3', '3');INSERT INTO `tb_school` VALUES ('4', '4', '4');INSERT INTO `tb_school` VALUES ('5', '5', '5');INSERT INTO `tb_school` VALUES ('6', 'a', 'a');INSERT INTO `tb_school` VALUES ('7', 'b', 'b');INSERT INTO `tb_school` VALUES ('8', 'c', 'c');INSERT INTO `tb_school` VALUES ('9', 'd', 'd');INSERT INTO `tb_school` VALUES ('10', 'e', 'e');

表的结构如下：

或者这样查看

查看表的结构命令是：

explain tb_school（表名）;或者show colunms from tb_stu（表名）;或者mysql> use information_schemaDatabase changedmysql> select * from columns where table_name='tb_stu';

表tb_stu有五条数据，表tb_school有十条数据。
其中tb_stu 和 tb_school 相同的字段有五条相同是数据。
现在通过左连接查询：

SELECT    a.id, a.name,a.sex,    b.id as b_id,    b.name as b_name,b.schoolFROM    tb_stu aLEFT JOIN tb_school b ON a.name = b.name

从图中看到通过左连接查询结果有五条数据。
总结：左连接查询是以左表为基表，其中 on 是条件。有两张表或者多张表中有相同的字段才会查询出来。

Mysql 有连接:

SELECT    a.id, a.name,a.sex,    b.id as b_id,    b.name as b_name,b.schoolFROM    tb_stu aRIGHT JOIN tb_school b ON a.name = b.name

总结：从查询结果可以看到，右连接是以右边为基表，on 的条件不管有没有，都会以右连接的表为基表，故此，查询结果是10条记录。

内连接：

SELECT    a.id, a.name,a.sex,    b.id as b_id,    b.name as b_name,b.schoolFROM    tb_stu aINNER  JOIN tb_school b ON a.name = b.name

总结：内连接是将表中相同的条件查询出来

union关键字用法：

SELECT    a.id,    a.name,    a.sexFROM    tb_stu a    GROUP BY    a.nameUNION    SELECT        b.id AS b_id,        b.name AS b_name,        b.school    FROM        tb_school bGROUP BYb.name

从内连接查询结果可以看到：查询总记录数是15。
总结：内连接是将两张表中所有的结果都查询出来。