MySQL数据库小技能：内外连接

tw4ld6

理解SQL查找的过程是进行SQL优化的理论依据。

SQL查找的基本原理:

单表查找：按照WHERE要求过滤表中的记录，形成中间表（这个中间表对用户是不可见的）；而后按照SELECT的选取列选取相应的列进行返回最后结果。两表连接查找：对两表求积（笛卡尔积）并用ON要求和连接类型进行过滤形成中间表；而后按照WHERE要求过滤中间表的记录，并按照SELECT指定的列返回查找结果。多表连接查找：先对第1个和第二个表根据两表连接做查找，而后用查找结果和第三个表做连接查找，以此类推，直到所有的表都连接上为止，最后形成一个中间的结果表，而后按照WHERE要求过滤中间表的记录，并按照SELECT指定的列返回查找结果。

I 内连接 （inner join）

在一个表中能够找到在另一个表中与之对应的记录，来构成新的记录表示出来，舍弃表中在另一个表中无对应记录的记录。

inner join (inner能够省略)： 查出的是两张表的交集，两张表都有的才查出来

select * from 表A inner join 表B on 判断要求;

select r.*,m.merchant_name,m.facilitator_id,m.facilitator_name,m.facilitator_top_id,m.facilitator_top_name from risk_equipment_position r inner join mer_merchant m on r.merchant_id = m.merchant_id ORDER BY create_time DESC LIMIT 15 ; 复制代码

1.1 等值连接

一个表中的某个字段是另一个表的外键。

例如： emp表和dept表存在多对一的相关关系，由外键字段deptno来守护，即emp表来主动守护这一关系。

1.2 非等值连接

between and 来实现非等值连接；

select e.ename，e.sal，s.grade from emp e join salgrades on e.sal between s.losal and s.hisal；

1.3 自连接( nature join)

表中的字段之间存在引用关系，经过同一个表取区别别名来实现自己连接。

select e.empno，m.name from emp e join emp m on m.empno=e.mgr；

II 外连接

外连接,将驱动表中所有记录表示。

说明：

左表便是在“（LEFT OUTER JOIN）”关键字左边的表。右表是右边的。在三种类型的外连接中，OUTER 关键字是可省略的。

2.1 左外连接 （left join）

左外连接会将主表对应的所有信息表示，从表与之匹配的记录表示。

以左表为主表（查找所有）， 右表为辅表（无的表示null）

案例1:

select * from emp joindepton emp.deptno=dept.deptno(+); --加号在join 右边的表的属性上为左连接 select * from emp left join dept on emp.deptno=dept.deptno; 复制代码

案例2: 查找商户终端定位限制

selectr.*,m.merchant_name,m.facilitator_id,m.facilitator_name,m.facilitator_top_id,m.facilitator_top_namefrom risk_equipment_position r left join mer_merchant m onr.merchant_id = m.merchant_idORDER BY create_time DESC LIMIT 15 ; 复制代码

2.2 右外连接 （right join）

加号在join 左边的表的属性上为右连接

以右表为主表（查找所有记录）， 左表为辅表（无的表示null）

selectr.* ,m.merchant_name,m.facilitator_id,m.facilitator_name,m.facilitator_top_id,m.facilitator_top_namefrom risk_equipment_position r right join mer_merchant m onr.merchant_id = m.merchant_idORDER BY create_time DESC LIMIT 15 复制代码

2.3 全连接 （ full join）

将两表中所有匹配和不匹配记录表示出来：两个表的所有数据都展示出来

MySQL 不识别 FULL join，因此能够经过 union 来实现。

2.4 联合(合并)查询（union）

案例1：

select a.*, ad.* from test_a as a left join test_a_description as ad on a.id=ad.parent_id union select a.*, ad.* from test_a as a right join test_a_description as ad on a.id=ad.parent_id; 复制代码

案例2:

select r.*,m.merchant_name,m.facilitator_id,m.facilitator_name,m.facilitator_top_id,m.facilitator_top_name fromrisk_equipment_position rleft join mer_merchant m on r.merchant_id = m.merchant_id union selectr.*,m.merchant_name,m.facilitator_id,m.facilitator_name,m.facilitator_top_id,m.facilitator_top_namefrom t_risk_equipment_position r right join t_mer_merchant m on r.merchant_id = m.merchant_id ORDER BY create_time DESC LIMIT 15; 复制代码

III 案例：mybatis-plus 多表查找

3.1 多表查找：在mapper.xml 写语句和拼接查找要求

在mapper.xml 写语句,对应办法写到mapper里，service能够注入对应的mapper进行调用。

.xml

<select id="selectProfitTradeActivityList" resultType="com.dto.TradeActivityDto" parameterType="com.dto.GenerateTradeQuery"> select a.id,a.trade_no,a.activity_type,a.activity_code from trans_flow t inner join trans_flow_activity a on t.trade_no = a.trade_no <where> t.trade_state=S and a.pay_channel_id=XYD <if test="ps.startTradeTime != null"> and t.trade_time >= #{ps.startTradeTime} </if> </where> -- order by t.update_time desc </select> 复制代码@Mapper public interface TransFlowActivityMapper extends BaseMapper<TransFlowActivity> { List<GenerateTradeActivityDto> selectProfitTradeActivityList(@Param("ps")GenerateTradeQuery input); } 复制代码

3.2 多表相关：Java代码中书写语句和拼接查找要求

实现思路：

运用 Select注解书写sql语句采用apply办法自定义sql拼接要求， 运用要求构造器做为参数。驱动表表和辅表的参数拼接书写SQL

3.3 案例：左外连接

实现思路：

运用 Select注解书写sql语句/xml书写语句。采用apply办法自定义sql拼接要求， 运用wrapper要求构造器做为参数。

实现过程：

定义查找接口： mapper.java/Service.javaService层@Override publicList<RiskEquipmentPositionDto>getlist(LambdaQueryWrapper<RiskEquipmentPosition> input) { returnbaseMapper.getList(input); } 复制代码Mapper层：采用 ${ew.customSqlSegment}和@Param(Constants.WRAPPER)实现，将要求构造器做为参数。书写SQL 方式一：运用Select注解书写sql语句@Select("select r.* ,m.merchant_name,m.facilitator_id,m.facilitator_name,m.facilitator_top_id,m.facilitator_top_name from risk_equipment_position r left join mer_merchant m on r.merchant_id = m.merchant_id ${ew.customSqlSegment} ") List<RiskEquipmentPositionDto> getList(@Param(Constants.WRAPPER) LambdaQueryWrapper<RiskEquipmentPosition> input); 复制代码

where后面的内容便是要求构造器生成的部分

方式2: 运用xml书写语句

拼接查找要求LambdaQueryWrapper<RiskEquipmentPosition> lambda = new LambdaQueryWrapper<>(); lambda.orderByDesc(TRiskEquipmentPosition::getCreateTime); if (input.getStartTime() != null) { lambda.ge(RiskEquipmentPosition::getCreateTime, input.getStartTime()); } if (input.getEndTime() != null) { lambda.lt(RiskEquipmentPosition::getCreateTime, input.getEndTime()); }if (input.getState() != null) { lambda.eq(RiskEquipmentPosition::getState, input.getState()); }// 终端SN、商户号本表查找，其他关键词必须相关商户表进行查找。 if (null != input.getKeywordType() && StringUtils.isNotBlank(input.getKeyword())) { varkeyWordTypes =newArrayList<String>(); keyWordTypes.add(ERiskEquipmentPositionKeyWord.MER_ID.getCode()); keyWordTypes.add(ERiskEquipmentPositionKeyWord.FAC_ID.getCode()); keyWordTypes.add(ERiskEquipmentPositionKeyWord.TOP_ID.getCode()); Long longKeyWord =0L; if(keyWordTypes.contains(input.getKeywordType())) {try { longKeyWord = Long.parseLong(input.getKeyword()); } catch (Exception ex) { throwCommonException.create(ServerResponse.createByError("无效的编号")); } } switch (BaseEnum.getEnum(ERiskEquipmentPositionKeyWord.class, input.getKeywordType())) { caseSN: lambda.eq(RiskEquipmentPosition::getEquipmentSn, input.getKeyword());break; case MER_ID: lambda.apply("r.merchant_id = {0}",input.getKeyword()); break; caseMER_NAME: lambda.apply("m.merchant_name = {0}",input.getKeyword()); break; case FAC_ID: //apply办法用于拼接自定义的要求判断,倘若自定义的要求判断是必须独立的结果，就必要记得加括号。 lambda.apply("m.facilitator_id = {0}",input.getKeyword()); break; case FAC_NAME: lambda.apply("m.facilitator_name = {0}",input.getKeyword()); break; case TOP_ID: lambda.apply("m.faclitator_top_id= {0}",input.getKeyword()); break; case TOP_NAME: lambda.apply("m.facilitator_top_name= {0}",input.getKeyword()); break; } } // 分页查找PageHelper.startPage(input.getPageNum(), input.getPageSize()); List<RiskEquipmentPositionDto> positionList = tRiskEquipmentPositionService.getlist(lambda); PageInfo pageInfo =new PageInfo(positionList); PageHelper.clearPage(); return pageInfo; 复制代码调用查找接口 List<RiskEquipmentPositionDto> positionList = RiskEquipmentPositionService.getlist(lambda); 复制代码

IV 笛卡尔积 （cross join）

交差集: 把表A和表B的数据进行一个N*M的组合，即笛卡尔积。 SELECT * FROM TableA CROSS JOIN TableB

V 小结:

5.1 外连接的区别分析

外连接：外连不仅返回符合连接和查找要求的数据行，还返回不符合要求的有些行。

外连接分三类：左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）和全外连接（FULL OUTER JOIN）

三者的一起点是都返回符合连接要求和查找要求（即：内连接）的数据行。区别点如下：左外连接：还返回左表中不符合连接要求但符合查找要求的数据行。右外连接：还返回右表中不符合连接要求但符合查找要求的数据行。全外连接：还返回左表中不符合连接要求但符合查找要求的数据行，并且还返回右表中不符合连接要求但符合查找要求的数据行。

全外连接实质是上左外连接和右外连接的数学合集（去掉重复），即“全外 = 左外 UNION 右外”。

5.2 Column merchant_id in where clause is ambiguous

错误代码：SELECT count(0) FROM risk_equipment_position r LEFT JOIN mer_merchant m ON r.merchant_id = m.merchant_id WHERE (merchant_id = ?)

case MER_ID: lambda.eq(TRiskEquipmentPosition::getMerchantId, input.getKeyword()); break; 复制代码

原由：多表查找的时候几个表中同期显现了某个相同的列名，而在查找要求WHERE后面又无指定是那个表，而导致的。

处理方法：where 要求语句后查找某个字段要加上表名。能够运用mybatis的apply办法来拼接自定义的要求判断。

caseMER_ID: lambda.apply("r.merchant_id = {0}",input.getKeyword()); break; case MER_NAME: lambda.apply("m.merchant_name = {0}",input.getKeyword()); break; case FAC_ID: //apply办法用于拼接自定义的要求判断,倘若自定义的要求判断是必须独立的结果，就必要记得加括号。 lambda.apply("m.facilitator_id = {0}",input.getKeyword()); break; 复制代码

关联问题：查找结果里面有两个相同的列名，而无指定是哪个表运用的时候，sql查找前面加表名可避免显现错误。

VI 集合

集合查找中的字段个数和类型要一致，这叫作为select的同构。

select job from emp deptno=10 union all select job from emp deptno=20; 复制代码union all 并集，并且不去除重复union 并集，去除重复intersect 交集minus 差集

原文链接：

https://juejin.cn/post/7183296217126797369 源自：稀土掘金

dhfgh

谷歌外贸网站优化技术。