MySQL的连接查询

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;

​ 执行结果：
​

---

总结：

左连接会获取左边表的所有数据，即使右表的没有匹配的数据；

右连接会获取右边表的所有数据，即使左表的没有匹配的数据；

内连接只会获取两个表中字段匹配关系的记录