新建两张表
建表SQL:
CREATE TABLE `employee` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`dept_type` int(11) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
INSERT INTO `employee` VALUES (1, 'test1', 1);
INSERT INTO `employee` VALUES (2, 'test2', 2);
INSERT INTO `employee` VALUES (3, 'test3', 10);
CREATE TABLE `dept` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`dept_type` int(11) DEFAULT NULL,
`description` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
INSERT INTO `dept` VALUES (1, 1, 'qwe');
INSERT INTO `dept` VALUES (2, 1, 'rty');
INSERT INTO `dept` VALUES (3, 2, 'iop');
INSERT INTO `dept` VALUES (4, 3, 'hjk');
employee表
dept表
笛卡尔积:
SELECT * FROM employee,dept;
结果:
等值连接:
SELECT * FROM employee e,dept d
WHERE e.dept_type = d.dept_type ;
结果:
外连接(左外连接left join、右外连接right join)
/*左外连接*/
SELECT * FROM employee e LEFT JOIN dept d
ON e.dept_type = d.dept_type;
/*右外连接*/
SELECT * FROM employee e RIGHT JOIN dept d
ON e.dept_type = d.dept_type;
左外连接:
右外连接:
内连接:
SELECT * FROM employee e JOIN dept d
ON e.dept_type = d.dept_type;
SELECT * FROM employee e INNER JOIN dept d
ON e.dept_type = d.dept_type;
两个的结果是一样的:
不必过多解释,看结果就很明白区别在哪了