MySQL七种JOIN类型小结,
在开始之前,我们创建两个表用于演示将要介绍的其中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 NULL, `deptId` VARCHAR(11) NOT NULL, PRIMARY KEY (`id`), KEY `fk_dept_id` (`deptId`) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
初始化数据
七种JOIN
1. A ∩ B
SELECT < select_list > FROM TableA A INNER JOIN TableB B # 共有 ON A.Key = B.Key
2. A ( = A ∩ B + A* )
SELECT < select_list > FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key
3. B ( = A ∩ B + B* )
SELECT < select_list > FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key
4. A* ( = A - A ∩ B )
SELECT < select_list > FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key # ON时主表保留 WHERE B.Key IS NULL # 筛选A表数据
5. B* ( = B - A ∩ B )
SELECT < select_list > FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL
6. A ∪ B
SELECT < select_list > FROM TableA A FULL OUTER JOIN TableB B ## FULL OUTER 仅oracle支持 ON A.Key = B.Key
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七种JOIN类型详解的文章就介绍到这了,更多相关mysql join类型内容请搜索PHP之友以前的文章或继续浏览下面的相关文章希望大家以后多多支持PHP之友!
您可能感兴趣的文章:- MySQL联表查询基本操作之left-join常见的坑
- mysql中各种常见join连表查询实例总结
- MySQL 8.0.18 稳定版发布! Hash Join如期而至
- Mysql中的join操作
- 深入理解mysql的自连接和join关联
- Mysql中Join的使用实例详解
- Mysql inner join on的用法实例(必看)
本站文章为和通数据库网友分享或者投稿,欢迎任何形式的转载,但请务必注明出处.
同时文章内容如有侵犯了您的权益,请联系QQ:970679559,我们会在尽快处理。