mysql相关子查询_SQL子查询与连接查询研究

假设有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种方法查询结果均一致：

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查询，今天简单总结了下，希望对读者有帮助。