mysql 学习（JOIN篇章）

七中JOIN学习

/* 1 */
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key;
/* 2 */
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key;
/* 3 */
SELECT <select_list> FROM TableA A INNER JOIN TableB B ON A.Key = B.Key;
/* 4 */
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL;
/* 5 */
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL;
/* 6 */
SELECT <select_list> FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key;
/* MySQL不支持FULL OUTER JOIN这种语法 可以改成 1+2 */
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key
UNION
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key;
/* 7 */
SELECT <select_list> FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL OR B.Key IS NULL;
/* MySQL不支持FULL OUTER JOIN这种语法 可以改成 4+5 */
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL;
UNION
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL;

创建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 CHARACTER SET = 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 CHARACTER SET = 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);

查询表

mysql> select * from tbl_dept;
+----+----------+--------+
| id | deptName | locAdd |
+----+----------+--------+
|  1 | RD       | 11     |
|  2 | HR       | 12     |
|  3 | MK       | 13     |
|  4 | MIS      | 14     |
|  5 | FD       | 15     |
+----+----------+--------+
5 rows in set (0.14 sec)

mysql> select * from tbl_emp;
+----+------+--------+
| id | name | deptId |
+----+------+--------+
|  1 | z3   |      1 |
|  2 | z4   |      1 |
|  3 | z5   |      1 |
|  4 | w5   |      2 |
|  5 | w6   |      2 |
|  6 | s7   |      3 |
|  7 | s8   |      4 |
|  8 | s9   |     51 |
+----+------+--------+
8 rows in set (0.00 sec)

测试一：左连接查询(两表公有部分+A表独有部分)

select * from tbl_emp a left join tbl_dept b on a.deptId = b.id;

 测试二：右连接查询(两表公有部分+b表独有部分)

 select * from tbl_emp a right join tbl_dept b on a.deptId = b.id;

 测试三：内连接查询(两表公有部分)

select * from tbl_emp a inner join tbl_dept b on a.deptId = b.id;

  测试四：A表独有，在左连接基础上增加where条件

 select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null;

 测试五：B表独有，在右连接基础上增加where条件

select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.deptId is null;

 测试六：外连接Full join（就是求两个表集合的并集。从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录，然后加上左表中剩余的记录，最后加上右表中剩余的记录）

mySql 不支持 full join，执行（select * from tbl_emp a full outer join tbl_dept b on a.deptId = b.id;）报错

因为外连接，他还有一种概念的说法是：left join 和 right join 的结果加起来再去重

所以可以用 union 连接 left join 和 right join 的结果来替换 outer join

select * from tbl_emp a left join tbl_dept b on a.deptId = b.id 
    -> union
    -> select * from tbl_emp a right join tbl_dept b on a.deptId = b.id;

 

测试七：A与B的独有

select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null  union select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.deptId is null;