日日操夜夜添-日日操影院-日日草夜夜操-日日干干-精品一区二区三区波多野结衣-精品一区二区三区高清免费不卡

MySQL的JOIN大家在日常工作中都用得很多，今天小朱老師就給大家整理了MySQL七種JOIN類型的用法，一起來深入了解一下吧。數(shù)據(jù)分析優(yōu)質(zhì)社群，等你加入哦~

首先創(chuàng)建需要JOIN的兩個(gè)表：

CREATE TABLE `tbl_emp` (
   `id` INT(11) NOT NULL AUTO_INCREMENT,
   `name` VARCHAR(30) DEFAULT NULL,
   `deptID` VARCHAR(40) DEFAULT NULL,
   PRIMARY KEY (`id`)
) 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;

我們可以看到數(shù)據(jù)的呈現(xiàn)：

1、A ∩ B

 SELECT *
 FROM tbl_emp a
 INNER JOIN tbl_dept b # 共有
 ON a.deptID = b.ID

2、A ( = A ∩ B + A* )

 SELECT *
 FROM tbl_emp a
 LEFT JOIN tbl_dept b
 ON a.deptID = b.ID

3、B ( = A ∩ B + B* )

 SELECT *
 FROM tbl_emp a
 RIGHT JOIN tbl_dept b
 ON a.deptID = b.ID

4. A* ( = A - A ∩ B )

 SELECT *
 FROM tbl_emp a
 LEFT JOIN tbl_dept b 
 ON a.deptID = b.ID # ON時(shí)主表保留
 WHERE B.ID IS NULL # 篩選A表數(shù)據(jù)

5. B* ( = B - A ∩ B )

 SELECT *
 FROM tbl_emp a
 RIGHT JOIN tbl_dept b 
 ON a.deptID = b.ID # ON時(shí)主表保留
 WHERE a.deptID IS NULL # 篩選B表數(shù)據(jù)

6. A ∪ B

 SELECT < select_list >
 FROM TableA A
 FULL OUTER JOIN TableB B ## FULL OUTER 僅oracle支持
 ON A.Key = B.Key


-- MySQL格式
 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

7. A ∪ B - A ∩ B

 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格式
 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
 WHERE a.deptID IS NULL OR b.ID IS NULL

以上就是其中JOIN類型的介紹，大家可以用起來了哦。