Mysql内连接、左连接、右连接查询

建表语句如下

创建表a_table：

-- ----------------------------
-- Table structure for a_table
-- ----------------------------
DROP TABLE IF EXISTS `a_table`;
CREATE TABLE `a_table` (
  `id` smallint(6)  AUTO_INCREMENT,
  `name` varchar(10) Not NULL,
  `phone` varchar(11) DEFAULT NULL,
  PRIMARY KEY ( `id` )
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of a_table
-- ----------------------------
INSERT INTO `a_table` VALUES ('1', '小张', '13000000000');
INSERT INTO `a_table` VALUES ('2', '小刘', '15200000000');
INSERT INTO `a_table` VALUES ('3', '老罗', '15300000000');
INSERT INTO `a_table` VALUES ('4', '老王', '13700000000');
INSERT INTO `a_table` VALUES ('5', '小吴', '13937399793');

创建表b_table：

-- ----------------------------
-- Table structure for b_table
-- ----------------------------
DROP TABLE IF EXISTS `b_table`;
CREATE TABLE `b_table` (
  `id` smallint(6) AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `phone` varchar(11) DEFAULT NULL,
  PRIMARY KEY ( `id` )
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of b_table
-- ----------------------------
INSERT INTO `b_table` VALUES ('1', '小张', '13000000000');
INSERT INTO `b_table` VALUES ('2', '老刘', '15200000000');
INSERT INTO `b_table` VALUES ('3', '老罗', '15300000000');
INSERT INTO `b_table` VALUES ('4', '小李', '13800000000');
INSERT INTO `b_table` VALUES ('5', '小刘', null);
INSERT INTO `b_table` VALUES ('6', '诸葛', null);

内连接查询

SQL 关键字 INNER JOIN,在表中存在至少一个匹配时，INNER JOIN 关键字返回行。

select a.*from a_table a INNER JOIN b_table  b on a.id=b.id where a.name=b.name 

查询结果如下:

从执行结果分析可知，内连接查询出来的数据是a_table和b_table公有数据。即下图红色阴影部分区间。

左连接查询

SQL 关键字 LEFT JOIN，从左表 (a_table) 那里返回所有的行，即使在右表 (b_table) 中没有匹配的行。

SELECT *from a_table a LEFT JOIN b_table b ON a.id=b.id 

查询结果如下：

从执行左连接查询的SQL结果可知，返回的数据是a_table的全部记录，并把在右表中缺少的记录用NULL表示。

右连接查询

SQL 关键字 RIGHT JOIN，从右表 (b_table) 那里返回所有的行，即使在左表 (a_table) 中没有匹配的行。

SELECT *from a_table a LEFT JOIN b_table b ON a.id=b.id

查询结果如下：

从执行右连接SQL结果可知，返回的数据是b_table的全部记录，并把在左表中缺少的记录用NULL表示

全连接

由于Mysql不支持FULL JOIN，但是可以通过LEFT JOIN + UNION + RIGHT JOIN来实现全连接。

SELECT *from a_table a LEFT JOIN b_table b ON a.id=b.id UNION 
SELECT *from a_table a RIGHT JOIN b_table b ON a.id=b.id

执行结果如下：

从执行结果可知，第六行的左表记录为NULL，(a_table总共五条记录)，右表中存在六条记录因此在做全连接会将两个表做一个并集。

无论左连接、右连接、内连接都用到了关键字 on 来匹配条件，那么on与where的区别在哪呢？

以左连接为例，使用on匹配a.name=b.name。

SELECT *from a_table a LEFT JOIN b_table b ON a.id=b.id and a.name=b.name

查询结果

使用where匹配a.name=b.name。

SELECT *from a_table a LEFT JOIN b_table b ON a.id=b.id where a.name=b.name

查询结果

从查询的结果分析可知，on是匹配建立临时表时的条件，而where是匹配临时表建立完成后的条件。