运维开发网

MySQL七种JOIN类型小结

运维开发网 https://www.qedev.com 2022-04-02 01:12 出处:网络 作者: IllTamer
在开始之前,我们创建两个表用于演示将要介绍的其中JOIN类型。建表CREATETABLE`tbl_dept`(`id`INT(11)NOTNULLAUTO_INCREMENT,...

        在开始之前,我们创建两个表用于演示将要介绍的其中JOIN类型。

建表

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;
CREATE TABLE `tbl_emp` (
   `id` INT(11) NOT NULL AUTO_INCREMENT,
   `name` VARCHAR(20) DEFAULT NULEjZvAL,
   `deptId` VARCHAR(11) NOT NULL,
   PRIMARY KEY (`id`),
   KEY `fk_dept_id` (`deptId`)
) Ehttp://www.cppcns.comNGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

初始化数据

MySQL七种JOIN类型小结

MySQL七种JOIN类型小结

七种JOIN

1. A ∩ B

MySQL七种JOIN类型小结

 SELECT < select_list >
 FROM TableA A
 INNER JOIN TableB B # 共有
 ON A.Key = B.Key

MySQL七种JOIN类型小结

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

MySQL七种JOIN类型小结

 SELECT < select_list >
 FROM TableA A
 LEFT JOIN TableB B
 ON A.Key = B.Key

MySQL七种JOIN类型小结

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

MySQL七种JOIN类型小结

 SELECT < select_list >
 FROM TableA A
 RIGHT JOINEjZvA TableB B
 ON A.Key = B.Key

MySQL七种JOIN类型小结

 

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

 

MySQL七种JOIN类型小结

 SELECT < select_list >
 FROM TableA A
 LEFT JOIN TableB B
 ON A.KEjZvAey = B.Key # ON时主表保留
 WHERE B.Key IS NULL # 筛选A表数据

MySQL七种JOIN类型小结

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

MySQL七种JOIN类型小结

 SELECT < select_list >
 FROM TableA A
 RIGHT JOIN TableB B
 ON A.Key = B.Key
 WHERE A.Key IS NULL

MySQL七种JOIN类型小结

6. A ∪ B

 

MySQL七种JOIN类型小结

 SELECT &lwww.cppcns.comt; select_list >
 FROM TableA A
 FULL OUTER JOIN TableB B ## FULL OUTER 仅oracle支持
 ON A.Key = B.Key

 

MySQL七种JOIN类型小结

7. A ∪ B - A ∩ B

MySQL七种JOIN类型小结

 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七种JOIN类型小结

到此这篇关于mysql七种JOIN类型详解的文章就介绍到这了,更多相关mysql join类型内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号