sql语句中关于join的基本相关(一)
程序员文章站
2022-06-22 15:38:41
...
一直以来,对于sql都是并没有花费时间与精力在上面,所以对于sql语句,一般都是秉承着会使用基本的增删查改就可以了,但是最近同事遇到了一个sql优化的问题,搞了好久,虽然最终废了好大的劲搞定了,但是还是感觉到了sql基础的薄弱,这才打算把sql相关的重新学习一遍,而关于join,这是第一篇。
首先,先甩出来一张已经被用烂了的图,如下:
由图可知,join大致分为内连接,外连接,右连接,左连接,自然连接。
首先,建两张表,emp、dep:
插入对应的数据:
笛卡尔积:CROSS JOIN:
要理解各种JOIN首先要理解笛卡尔积。笛卡尔积就是将A表的每一条记录与B表的每一条记录强行拼在一起。所以,如果A表有n条记录,B表有m条记录,笛卡尔积产生的结果就会产生n*m条记录。下面的例子,emp有8条记录,dep有5条记录,所有他们俩的笛卡尔积有40条记录。有五种产生笛卡尔积的方式如下:
查询结果如下:
内连接:INNER JOIN:
内连接INNER JOIN是最常用的连接操作。从数学的角度讲就是求两个表的交集,从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录。有INNER JOIN,WHERE(等值连接),STRAIGHT_JOIN,JOIN(省略INNER)四种写法,示例如下:
查询结果如下:
左连接:LEFT JOIN
左连接LEFT JOIN的含义就是求两个表的交集外加左表剩下的数据。依旧从笛卡尔积的角度讲,就是先从笛卡尔积中挑出ON子句条件成立的记录,然后加上左表中剩余的记录,写法如下:
查询结果如下,见最后一条:
右连接:RIGHT JOIN
同理右连接RIGHT JOIN就是求两个表的交集外加右表剩下的数据。再次从笛卡尔积的角度描述,右连接就是从笛卡尔积中挑出ON子句条件成立的记录,然后加上右表中剩余的记录,写法如下:
查询结果如下,见最后一条:
外连接:OUTER JOIN
外连接就是求两个集合的并集。从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录,然后加上左表中剩余的记录,最后加上右表中剩余的记录。另外MySQL不支持OUTER JOIN,但是我们可以对左连接和右连接的结果做UNION操作来实现,写法如下:
查询结果如下:
自然连接:NATURE JOIN
自然连接就是找出两个表中相同的列作为连接条件进行连接,写法如下:
查询结果如下:
下一篇具体介绍一下,开始的时候那张图的几种具体的sql写法。
首先,先甩出来一张已经被用烂了的图,如下:
由图可知,join大致分为内连接,外连接,右连接,左连接,自然连接。
首先,建两张表,emp、dep:
CREATE TABLE `emp` ( `id` int(11) NOT NULL, `empName` varchar(20) COLLATE utf8_bin DEFAULT NULL, `deptId` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin CREATE TABLE `dep` ( `id` int(11) NOT NULL, `deptName` varchar(20) COLLATE utf8_bin DEFAULT NULL, `locAdd` varchar(50) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
插入对应的数据:
insert into `emp` (`id`, `empName`, `deptId`) values('1','e1','1'); insert into `emp` (`id`, `empName`, `deptId`) values('2','e2','1'); insert into `emp` (`id`, `empName`, `deptId`) values('3','e3','1'); insert into `emp` (`id`, `empName`, `deptId`) values('4','e4','2'); insert into `emp` (`id`, `empName`, `deptId`) values('5','e5','2'); insert into `emp` (`id`, `empName`, `deptId`) values('6','e6','3'); insert into `emp` (`id`, `empName`, `deptId`) values('7','e7','4'); insert into `emp` (`id`, `empName`, `deptId`) values('8','e8','51'); insert into `dep` (`id`, `deptName`, `locAdd`) values('1','RD','11'); insert into `dep` (`id`, `deptName`, `locAdd`) values('2','HR','12'); insert into `dep` (`id`, `deptName`, `locAdd`) values('3','MK','13'); insert into `dep` (`id`, `deptName`, `locAdd`) values('4','MIS','14'); insert into `dep` (`id`, `deptName`, `locAdd`) values('5','FD','15');
笛卡尔积:CROSS JOIN:
要理解各种JOIN首先要理解笛卡尔积。笛卡尔积就是将A表的每一条记录与B表的每一条记录强行拼在一起。所以,如果A表有n条记录,B表有m条记录,笛卡尔积产生的结果就会产生n*m条记录。下面的例子,emp有8条记录,dep有5条记录,所有他们俩的笛卡尔积有40条记录。有五种产生笛卡尔积的方式如下:
SELECT * FROM emp CROSS JOIN dep; SELECT * FROM emp INNER JOIN dep; SELECT * FROM emp ,dep; SELECT * FROM emp NATURE JOIN dep; SELECT * FROM emp NATURA JOIN dep;
查询结果如下:
id empName deptId id deptName locAdd 1 e1 1 1 RD 11 1 e1 1 2 HR 12 1 e1 1 3 MK 13 1 e1 1 4 MIS 14 1 e1 1 5 FD 15 2 e2 1 1 RD 11 2 e2 1 2 HR 12 2 e2 1 3 MK 13 2 e2 1 4 MIS 14 2 e2 1 5 FD 15 3 e3 1 1 RD 11 3 e3 1 2 HR 12 3 e3 1 3 MK 13 3 e3 1 4 MIS 14 3 e3 1 5 FD 15 4 e4 2 1 RD 11 4 e4 2 2 HR 12 4 e4 2 3 MK 13 4 e4 2 4 MIS 14 4 e4 2 5 FD 15 5 e5 2 1 RD 11 5 e5 2 2 HR 12 5 e5 2 3 MK 13 5 e5 2 4 MIS 14 5 e5 2 5 FD 15 6 e6 3 1 RD 11 6 e6 3 2 HR 12 6 e6 3 3 MK 13 6 e6 3 4 MIS 14 6 e6 3 5 FD 15 7 e7 4 1 RD 11 7 e7 4 2 HR 12 7 e7 4 3 MK 13 7 e7 4 4 MIS 14 7 e7 4 5 FD 15 8 e8 51 1 RD 11 8 e8 51 2 HR 12 8 e8 51 3 MK 13 8 e8 51 4 MIS 14 8 e8 51 5 FD 15
内连接:INNER JOIN:
内连接INNER JOIN是最常用的连接操作。从数学的角度讲就是求两个表的交集,从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录。有INNER JOIN,WHERE(等值连接),STRAIGHT_JOIN,JOIN(省略INNER)四种写法,示例如下:
SELECT * FROM emp INNER JOIN dep ON emp.`deptId`=dep.`id`; SELECT * FROM emp,dep WHERE emp.`deptId`=dep.`id`; SELECT * FROM emp STRAIGHT_JOIN dep ON emp.`deptId`=dep.`id`; SELECT * FROM emp JOIN dep ON emp.`deptId`=dep.`id`
查询结果如下:
id empName deptId id deptName locAdd 1 e1 1 1 RD 11 2 e2 1 1 RD 11 3 e3 1 1 RD 11 4 e4 2 2 HR 12 5 e5 2 2 HR 12 6 e6 3 3 MK 13 7 e7 4 4 MIS 14
左连接:LEFT JOIN
左连接LEFT JOIN的含义就是求两个表的交集外加左表剩下的数据。依旧从笛卡尔积的角度讲,就是先从笛卡尔积中挑出ON子句条件成立的记录,然后加上左表中剩余的记录,写法如下:
SELECT * FROM emp LEFT JOIN dep ON emp.`deptId`=dep.`id`;
查询结果如下,见最后一条:
id empName deptId id deptName locAdd 1 e1 1 1 RD 11 2 e2 1 1 RD 11 3 e3 1 1 RD 11 4 e4 2 2 HR 12 5 e5 2 2 HR 12 6 e6 3 3 MK 13 7 e7 4 4 MIS 14 8 e8 51 NULL NULL NULL
右连接:RIGHT JOIN
同理右连接RIGHT JOIN就是求两个表的交集外加右表剩下的数据。再次从笛卡尔积的角度描述,右连接就是从笛卡尔积中挑出ON子句条件成立的记录,然后加上右表中剩余的记录,写法如下:
SELECT * FROM emp RIGHT JOIN dep ON emp.`deptId`=dep.`id`;
查询结果如下,见最后一条:
id empName deptId id deptName locAdd 1 e1 1 1 RD 11 2 e2 1 1 RD 11 3 e3 1 1 RD 11 4 e4 2 2 HR 12 5 e5 2 2 HR 12 6 e6 3 3 MK 13 7 e7 4 4 MIS 14 NULL NULL NULL 5 FD 15
外连接:OUTER JOIN
外连接就是求两个集合的并集。从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录,然后加上左表中剩余的记录,最后加上右表中剩余的记录。另外MySQL不支持OUTER JOIN,但是我们可以对左连接和右连接的结果做UNION操作来实现,写法如下:
SELECT * FROM emp LEFT JOIN dep ON emp.`deptId`=dep.`id` UNION SELECT * FROM emp RIGHT JOIN dep ON emp.`deptId`=dep.`id`;
查询结果如下:
id empName deptId id deptName locAdd 1 e1 1 1 RD 11 2 e2 1 1 RD 11 3 e3 1 1 RD 11 4 e4 2 2 HR 12 5 e5 2 2 HR 12 6 e6 3 3 MK 13 7 e7 4 4 MIS 14 8 e8 51 NULL NULL NULL NULL NULL NULL 5 FD 15
自然连接:NATURE JOIN
自然连接就是找出两个表中相同的列作为连接条件进行连接,写法如下:
SELECT * FROM emp NATURAL JOIN dep;
查询结果如下:
id empName deptId deptName locAdd 1 e1 1 RD 11 2 e2 1 HR 12 3 e3 1 MK 13 4 e4 2 MIS 14 5 e5 2 FD 15
下一篇具体介绍一下,开始的时候那张图的几种具体的sql写法。
上一篇: validation实现自定义注解