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

欢迎大家来到IT世界,在知识的湖畔探索吧!

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

SQL查询的基本原理:

• 单表查询：根据WHERE条件过滤表中的记录，形成中间表（这个中间表对用户是不可见的）；然后根据SELECT的选择列选择相应的列进行返回最终结果。
• 两表连接查询：对两表求积（笛卡尔积）并用ON条件和连接类型进行过滤形成中间表；然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果。
• 多表连接查询：先对第一个和第二个表按照两表连接做查询，然后用查询结果和第三个表做连接查询，以此类推，直到所有的表都连接上为止，最终形成一个中间的结果表，然后根据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 ; 复制代码

欢迎大家来到IT世界,在知识的湖畔探索吧!

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:

欢迎大家来到IT世界,在知识的湖畔探索吧!select * from emp join dept on emp.deptno=dept.deptno(+); --加号在join 右边的表的属性上为左连接 select * from emp left join dept on emp.deptno=dept.deptno; 复制代码

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

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 ORDER BY create_time DESC LIMIT 15 ; 复制代码

2.2 右外连接 （right join）

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

以右表为主表（查询全部记录）， 左表为辅表（没有的显示null）

欢迎大家来到IT世界,在知识的湖畔探索吧!select r.* ,m.merchant_name,m.facilitator_id,m.facilitator_name,m.facilitator_top_id,m.facilitator_top_name from risk_equipment_position r right join mer_merchant m on r.merchant_id = m.merchant_id ORDER 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 from risk_equipment_position r left join mer_merchant m on r.merchant_id = m.merchant_id union select r.*,m.merchant_name,m.facilitator_id,m.facilitator_name,m.facilitator_top_id,m.facilitator_top_name from 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拼接条件， 使用条件构造器作为参数。

1. 驱动表表和辅表的参数拼接
2. 书写SQL

3.3 案例：左外连接

实现思路：

• 使用 Select注解书写sql语句/xml书写语句。
• 采用apply方法自定义sql拼接条件， 使用wrapper条件构造器作为参数。

实现步骤：

1. 定义查询接口： mapper.java/Service.java

• Service层

@Override public List<RiskEquipmentPositionDto> getlist(LambdaQueryWrapper<RiskEquipmentPosition> input) { return baseMapper.getList(input); } 复制代码

2. Mapper层：采用 ${ew.customSqlSegment}和@Param(Constants.WRAPPER)实现，将条件构造器作为参数。
3. 书写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书写语句

4. 拼接查询条件

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())) { var keyWordTypes = new ArrayList<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) { throw CommonException.create(ServerResponse.createByError("无效的编号")); } } switch (BaseEnum.getEnum(ERiskEquipmentPositionKeyWord.class, input.getKeywordType())) { case SN: lambda.eq(RiskEquipmentPosition::getEquipmentSn, input.getKeyword()); break; case MER_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; 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; 复制代码

5. 调用查询接口

 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）

1. 三者的共同点是都返回符合连接条件和查询条件（即：内连接）的数据行。
2. 不同点如下：

• 左外连接：还返回左表中不符合连接条件但符合查询条件的数据行。
• 右外连接：还返回右表中不符合连接条件但符合查询条件的数据行。
• 全外连接：还返回左表中不符合连接条件但符合查询条件的数据行，并且还返回右表中不符合连接条件但符合查询条件的数据行。

全外连接实际是上左外连接和右外连接的数学合集（去掉重复），即“全外 = 左外 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方法来拼接自定义的条件判断。

 case MER_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/
来源：稀土掘金