美文网首页
SQL子查询与连接查询研究

SQL子查询与连接查询研究

作者: 断翅绝尘 | 来源:发表于2018-08-21 19:07 被阅读0次

假设有A、B两张表,其中B表有A表的外键。在SQL查询中,我们经常有这样的需求,需要根据B表中的条件筛选去查询A表中的内容,以工作流查询用户的已办流程为例来说明:
1、历史流程实例表act_hi_procinst:下述用A表代替

CREATE TABLE `act_hi_procinst` (
  `ID_` varchar(64) COLLATE utf8_bin NOT NULL,
  `PROC_INST_ID_` varchar(64) COLLATE utf8_bin NOT NULL,
  `BUSINESS_KEY_` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `PROC_DEF_ID_` varchar(64) COLLATE utf8_bin NOT NULL,
  `START_TIME_` datetime(3) NOT NULL,
  `END_TIME_` datetime(3) DEFAULT NULL,
  `DURATION_` bigint(20) DEFAULT NULL,
  `START_USER_ID_` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `START_ACT_ID_` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `END_ACT_ID_` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `SUPER_PROCESS_INSTANCE_ID_` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `DELETE_REASON_` varchar(4000) COLLATE utf8_bin DEFAULT NULL,
  `TENANT_ID_` varchar(255) COLLATE utf8_bin DEFAULT '',
  `NAME_` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`ID_`),
  UNIQUE KEY `PROC_INST_ID_` (`PROC_INST_ID_`),
  KEY `ACT_IDX_HI_PRO_INST_END` (`END_TIME_`),
  KEY `ACT_IDX_HI_PRO_I_BUSKEY` (`BUSINESS_KEY_`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

2、历史节点权限办理表ACT_HI_IDENTITYLINK:以下用B表代替

CREATE TABLE `act_hi_identitylink` (
  `ID_` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `GROUP_ID_` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `TYPE_` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `USER_ID_` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `TASK_ID_` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `PROC_INST_ID_` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`ID_`),
  KEY `ACT_IDX_HI_IDENT_LNK_USER` (`USER_ID_`),
  KEY `ACT_IDX_HI_IDENT_LNK_TASK` (`TASK_ID_`),
  KEY `ACT_IDX_HI_IDENT_LNK_PROCINST` (`PROC_INST_ID_`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

3、A、B表关系说明
A表的PROC_INST_ID_字段与主键ID_字段值完全相同,B表的PROC_INST_ID_字段作为A表的外键。
一个流程对应A表的一条数据,但会对应B表的多条数据(每个流程节点在办理时都会往B表插入数据),B表的USER_ID_字段会记录办理用户的ID,查询用户已办流程即根据此字段过滤即可,好了背景介绍完毕。
4、查询用户已办流程SQL写法
1)子查询(in方法)

SELECT DISTINCT
    RES.*
FROM
    ACT_HI_PROCINST RES
WHERE
    RES.PROC_INST_ID_ IN (
        SELECT
            LINK.PROC_INST_ID_
        FROM
            ACT_HI_IDENTITYLINK LINK
        WHERE
            LINK.USER_ID_ = 6742
    )

2)子查询(exists方法)

SELECT DISTINCT
    RES.*
FROM
    ACT_HI_PROCINST RES
WHERE
    (
        EXISTS (
            SELECT
                LINK.USER_ID_
            FROM
                ACT_HI_IDENTITYLINK LINK
            WHERE
                USER_ID_ = 6742
            AND LINK.PROC_INST_ID_ = RES.PROC_INST_ID_
        )
    )

3)连接查询(join方法)

SELECT DISTINCT
    RES.*
FROM
    ACT_HI_PROCINST RES
JOIN ACT_HI_IDENTITYLINK LINK ON LINK.PROC_INST_ID_ = RES.PROC_INST_ID_
WHERE
    LINK.USER_ID_ = 6742

以上3种方法查询结果均一致:


image.png

5、sql语句对比
1)查询速度
当外层查询结果集的数据量N较小时,优先选用方法2——子查询(exists方法);
反之,当外层查询结果集的数据量N较大时,优先推荐方法3——连接查询(join方法);
2)如何选择
分情况:
如果最后只要求查询A表种的字段内容, 并且外层查询结果集的数据量N较小时则优先推荐方法2——子查询(exists方法);
如果要求查询的内容包含A、B两个表的字段,或者外层查询结果集的数据量N较大时则优先推荐方法3——连接查询(join方法);
至于方法一——子查询(in方法)基本不太建议,可读性和效率均不佳,除非in后面是确定的范围,如b.status in(1,2,3,4)这种;如果你用了方法一不妨看看能否用其他两种方法代替,以提升可读性和效率。
本文就到这儿了,平时经常用的sql查询,今天简单总结了下,希望对读者有帮助。

相关文章

  • SQL子查询与连接查询研究

    假设有A、B两张表,其中B表有A表的外键。在SQL查询中,我们经常有这样的需求,需要根据B表中的条件筛选去查询A表...

  • EF Core 备忘

    模糊查询sql linq 内连接查询sql linq 左连接查询sql linq 左连接查询(连接内带条件)sql...

  • SQL语言之查询

    SQL语言之查询(二) 前言 本章我们将学习SQL查询中的高级部分,如内连接、外连接和子查询,通过这些查询技术我们...

  • 好程序员Java培训分享SQL语言之查询

    好程序员Java培训分享SQL语言之查询,本章我们将学习SQL查询中的高级部分,如内连接、外连接和子查询,通过这些...

  • 数据分析之SQL子查询

    文章阅读路线: SQL子查询概念 独立子查询实例 相关子查询实例 SQL子查询常见玩伴 1.SQL子查询概念 子查...

  • SQL连接查询研究

    1、分类在多表关联查询时,经常要用到连接查询,SQL中连接分为四种:内连接(inner join或join)、左外...

  • 数据库查询语句

    条件与逻辑查询 模糊查询 范围查询 排序 聚合函数 分组 连接查询 分页 子查询

  • sql

    sql语句 查询 简单查询 例: 多表连接查询 例: 2.更新

  • Oracel_子查询

    SQL子查询 子查询语法 子查询 (内查询) 在主查询之前一次执行完成。 子查询的结果被主查询(外查询)使用 。 ...

  • 《SQL必知必会》笔记6-子查询、联结表join

    1 使用子查询 查询(query):任何SQL语句都是查询,但一般指SELECT语句。 SQL还允许创建子查询(s...

网友评论

      本文标题:SQL子查询与连接查询研究

      本文链接:https://www.haomeiwen.com/subject/pumjiftx.html