创建“部门”表
CREATE TABLE department(
id INT,
NAME VARCHAR(50)
);
创建“员工”表
CREATE TABLE employee(
id INT,
NAME VARCHAR(50),
depno INT
);
填入数据
部门表
员工表
内连接
SELECT e.`id`,e.`name` AS '员工名',d.`name` AS '部门' FROM employee e,department d WHERE e.`depno` = d.`id`;
SELECT e.`id`,e.`name` AS '员工名',d.`name` AS '部门' FROM employee e INNER JOIN department d ON e.`depno` = d.`id`;
外连接(左连接,右连接)
左连接
SELECT e.`id`,e.`name` AS '员工名',d.`name` AS '部门' FROM employee e LEFT OUTER JOIN department d ON e.`depno` = d.`id`;
右连接
SELECT e.`id`,e.`name` AS '员工名',d.`name` AS '部门' FROM employee e RIGHT OUTER JOIN department d ON e.`depno` = d.`id`;