MySQL的连接查询
左表数据表结构
DROP TABLE IF EXISTS `employee_tbl`;
CREATE TABLE `employee_tbl` (
`id` int(11) NOT NULL,
`name` char(10) NOT NULL DEFAULT '',
`date` datetime NOT NULL,
`singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
右表数据表结构
CREATE TABLE IF NOT EXISTS `runoob_tbl`(
`runoob_id` INT UNSIGNED AUTO_INCREMENT,
`runoob_title` VARCHAR(100) NOT NULL,
`runoob_author` VARCHAR(40) NOT NULL,
`submission_date` DATE,
PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
1. 左连接LEFT JOIN
SELECT * FROM employee_tbl e LEFT JOIN runoob_tbl r ON r.runoob_id=e.id;
执行结果:
2. 右连接RIGHT JOIN
SELECT * FROM employee_tbl e RIGHT JOIN runoob_tbl r ON r.runoob_id=e.id;
执行结果:
3. 内连接INNER JOIN
SELECT * FROM employee_tbl e INNER JOIN runoob_tbl r ON r.runoob_id=e.id;
(INNER JOIN可简写为JOIN)
等价于
SELECT * FROM employee_tbl e,runoob_tbl r WHERE r.runoob_id=e.id;
执行结果:
总结:
左连接会获取左边表的所有数据,即使右表的没有匹配的数据;
右连接会获取右边表的所有数据,即使左表的没有匹配的数据;
内连接只会获取两个表中字段匹配关系的记录