数据库小技能：内外连接

2022-12-27 153

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

简介： 在一个表中能够找到在另一个表中与之对应的记录，来组成新的记录显示出来，舍弃表中在另一个表中没有对应记录的记录。inner join (inner可以省略)：查出的是两张表的交集，两张表都有的才查出来

引言

理解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 ;

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 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）
`

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 &gt;= #{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.java

Service层

@Override
    public List<RiskEquipmentPositionDto> getlist(LambdaQueryWrapper<RiskEquipmentPosition> input) {
        return baseMapper.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())) {

            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;

调用查询接口

        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方法来拼接自定义的条件判断。

                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 差集

数据库小技能：内外连接

引言

I 内连接（inner join）

1.1 等值连接

1.2 非等值连接

1.3 自连接( nature join)

II 外连接

2.1 左外连接（left join）

2.2 右外连接（right join）

2.3 全连接（ full join）

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

III 案例：mybatis-plus 多表查询

3.1 多表查询：在mapper.xml 写语句和拼接查询条件

3.2 多表关联：Java代码中书写语句和拼接查询条件

3.3 案例：左外连接

IV 笛卡尔积（cross join）

V 小结:

5.1 外连接的区别分析

5.2 Column 'merchant_id' in where clause is ambiguous

VI 集合

see also

热门文章

最新文章

相关课程

相关电子书

相关实验场景

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

数据库小技能：内外连接

引言

I 内连接 （inner join）

1.1 等值连接

1.2 非等值连接

1.3 自连接( nature join)

II 外连接

2.1 左外连接 （left join）

2.2 右外连接 （right join）

2.3 全连接 （ full join）

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

III 案例：mybatis-plus 多表查询

3.1 多表查询：在mapper.xml 写语句和拼接查询条件

3.2 多表关联：Java代码中书写语句和拼接查询条件

3.3 案例：左外连接

IV 笛卡尔积 （cross join）

V 小结:

5.1 外连接的区别分析

5.2 Column 'merchant_id' in where clause is ambiguous

VI 集合

see also

热门文章

最新文章

相关课程

相关电子书

相关实验场景

I 内连接（inner join）

2.1 左外连接（left join）

2.2 右外连接（right join）

2.3 全连接（ full join）

IV 笛卡尔积（cross join）