以下图结合例子,自己敲一遍。
SQL语句的JOIN连接在开发中非常常用。
先看下面这张图,包括了内连接inner join,左连接left join,右连接 right join等。
以下两个表为例子,一个是tbl_emp,一个是tbl_dept。
CREATE TABLE `tbl_emp`( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, `deptId` int(11) DEFAULT NULL, PRIMARY KEY(`id`), KEY `fk_dept_id`(`deptId`) )ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET=UTF8; CREATE TABLE `tbl_dept`( `id` int(11) NOT NULL AUTO_INCREMENT, `deptName` varchar(30) DEFAULT NULL, `locAdd` VARCHAR(40) DEFAULT NULL, PRIMARY KEY(`id`) )ENGINE = INNODB AUTO_INCREMENT= 1 DEFAULT CHARSET=utf8; insert into tbl_dept(deptName,locAdd) values('RD',11); insert into tbl_dept(deptName,locAdd) values('HR',12); insert into tbl_dept(deptName,locAdd) values('MK',13); insert into tbl_dept(deptName,locAdd) values('MIS',14); insert into tbl_dept(deptName,locAdd) values('FD',15); insert into tbl_emp(NAME,deptId) values('z3',1); insert into tbl_emp(NAME,deptId) values('z4',1); insert into tbl_emp(NAME,deptId) values('z5',1); insert into tbl_emp(NAME,deptId) values('w5',2); insert into tbl_emp(NAME,deptId) values('w6',2); insert into tbl_emp(NAME,deptId) values('s7',3); insert into tbl_emp(NAME,deptId) values('s8',4); insert into tbl_emp(NAME,deptId) values('s9',51);
SELECT * from tbl_emp e INNER JOIN tbl_dept d ON e.deptId = d.id;
SELECT * from tbl_emp e left JOIN tbl_dept d ON e.deptId = d.id;
SELECT * from tbl_emp e RIGHT JOIN tbl_dept d ON e.deptId = d.id;
SELECT * from tbl_emp e left JOIN tbl_dept d ON e.deptId = d.id WHERE d.id is NULL;
SELECT * FROM tbl_emp e RIGHT JOIN tbl_dept d ON e.deptId = d.id WHERE e.id is NULL;
# 两表的左查询 (SELECT * FROM tbl_emp e LEFT JOIN tbl_dept d ON e.deptId = d.id) UNION # 两表的右查询 (SELECT * FROM tbl_emp e RIGHT JOIN tbl_dept d ON e.deptId = d.id);
SELECT * FROM tbl_emp e LEFT JOIN tbl_dept d ON e.deptId = d.id WHERE d.id is null UNION SELECT * FROM tbl_emp e RIGHT JOIN tbl_dept d ON e.deptId = d.id WHERE e.deptId is NULL