【MYSQL高级】Mysql 表的七种连接方式【附带练习sql】

文章目录

连接

七种JOIN介绍

图形连接方式说明SQL内连接共有部分SELECT <select_list> FROM TableA A INNER JOIN TableB B ON A.Key = B.Key;左连接A表独有+共有部分SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key;右连接B表独有+共有部分SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key;A的独有SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL;B的独有SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL;全连接A的独有+共有部分+B的独有SELECT <select_list> FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key; MySQL不支持FULL OUTER JOIN这种语法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不支持FULL OUTER JOIN这种语法

练习

建表+存储数据

CREATETABLE`tbl_emp`(`id`int(11)NOTNULLAUTO_INCREMENT,`name`varchar(20)DEFAULTNULL,`deptId`int(11)DEFAULTNULL,PRIMARYKEY(`id`),KEY`fk_dept_id`(`deptId`))ENGINE=InnoDBAUTO_INCREMENT=1CHARACTERSET= utf8;CREATETABLE`tbl_dept`(`id`int(11)NOTNULLAUTO_INCREMENT,`deptName`varchar(30)DEFAULTNULL,`locAdd`varchar(40)DEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=1CHARACTERSET= utf8;insertinto tbl_dept(deptName,locAdd)values('RD',11);insertinto tbl_dept(deptName,locAdd)values('HR',12);insertinto tbl_dept(deptName,locAdd)values('MK',13);insertinto tbl_dept(deptName,locAdd)values('MIS',14);insertinto tbl_dept(deptName,locAdd)values('FD',15);insertinto tbl_emp(NAME,deptId)values('z3',1);insertinto tbl_emp(NAME,deptId)values('z4',1);insertinto tbl_emp(NAME,deptId)values('z5',1);insertinto tbl_emp(NAME,deptId)values('w5',2);insertinto tbl_emp(NAME,deptId)values('w6',2);insertinto tbl_emp(NAME,deptId)values('s7',3);insertinto tbl_emp(NAME,deptId)values('s8',4);insertinto tbl_emp(NAME,deptId)values('s9',51);

笛卡尔积

select*from tbl_emp,tbl_dept;

5条数据

和

8条数据

两两组合，一共

40条

，结果如下

"id""name""deptId""id""deptName""locAdd""1""z3""1""5""FD""15""1""z3""1""4""MIS""14""1""z3""1""3""MK""13""1""z3""1""2""HR""12""1""z3""1""1""RD""11""2""z4""1""5""FD""15""2""z4""1""4""MIS""14""2""z4""1""3""MK""13""2""z4""1""2""HR""12""2""z4""1""1""RD""11""3""z5""1""5""FD""15""3""z5""1""4""MIS""14""3""z5""1""3""MK""13""3""z5""1""2""HR""12""3""z5""1""1""RD""11""4""w5""2""5""FD""15""4""w5""2""4""MIS""14""4""w5""2""3""MK""13""4""w5""2""2""HR""12""4""w5""2""1""RD""11""5""w6""2""5""FD""15""5""w6""2""4""MIS""14""5""w6""2""3""MK""13""5""w6""2""2""HR""12""5""w6""2""1""RD""11""6""s7""3""5""FD""15""6""s7""3""4""MIS""14""6""s7""3""3""MK""13""6""s7""3""2""HR""12""6""s7""3""1""RD""11""7""s8""4""5""FD""15""7""s8""4""4""MIS""14""7""s8""4""3""MK""13""7""s8""4""2""HR""12""7""s8""4""1""RD""11""8""s9""51""5""FD""15""8""s9""51""4""MIS""14""8""s9""51""3""MK""13""8""s9""51""2""HR""12""8""s9""51""1""RD""11"

内连接

SELECT*FROM tbl_emp a INNERJOIN tbl_dept b ON a.deptId = b.id;

左连接

SELECT*FROM tbl_emp a LEFTJOIN tbl_dept b ON a.deptId = b.id;

右连接

SELECT*FROM tbl_emp a RIGHTJOIN tbl_dept b ON a.deptId = b.id;

A表独有

SELECT*FROM tbl_emp a LEFTJOIN tbl_dept b ON a.deptId = b.id WHERE b.id isnull;

B表独有

SELECT*FROM tbl_emp a RIGHTJOIN tbl_dept b ON a.deptId = b.id WHERE a.deptId isnull;

全连接

尝试换一种方式实现：A的独有+共有+B的独有

实现方式一：左连接+右连接+公共部分去重

SELECT*FROM tbl_emp a LEFTJOIN tbl_dept b ON a.deptId = b.id 
UNIONSELECT*FROM tbl_emp a RIGHTJOIN tbl_dept b ON a.deptId = b.id;

实现方式二：左连接+B独有

SELECT*FROM tbl_emp a LEFTJOIN tbl_dept b ON a.deptId = b.id 
UNIONSELECT*FROM tbl_emp a RIGHTJOIN tbl_dept b ON a.deptId = b.id WHERE a.deptId isnull;

A独有+B独有

SELECT*FROM tbl_emp a LEFTJOIN tbl_dept b ON a.deptId = b.id WHERE b.id isnullUNIONSELECT*FROM tbl_emp a RIGHTJOIN tbl_dept b ON a.deptId = b.id WHERE a.deptId isnull;

文章说明

本文章为本人学习尚硅谷的学习笔记，文章中大部分内容来源于尚硅谷视频（点击学习尚硅谷相关课程），也有部分内容来自于自己的思考，发布文章是想帮助其他学习的人更方便地整理自己的笔记或者直接通过文章学习相关知识，如有侵权请联系删除，最后对尚硅谷的优质课程表示感谢。