SQL之异曲同工审计查询技巧

马承希

大数据审计环境下，审计人员通常需要对两个或更多存储大量数据的表格进行查询分析，以发现审计事项可能存在的问题疑点。传统的查询方式效率很低，难以满足大数据审计环境下快速、准确发现问题疑点的要求。使用sql server 语句，灵活运用Outer join外连接、except、in等多种查询技巧，举一反三、异曲同工，能够提高审计效果。

审计查询分析的本质是集合运算

集合理论认为，求解既属于集合A又属于集合B的那些元素的集合，称为A和B的交运算，用A∩B表示；求解不属于集合A但属于集合B的那些元素的集合，称为A和B的差运算，用A-B表示，反之则用B-A表示。审计事项的大量信息都以表的形式存储在数据库中，这些表构成了一个个有限的集合，每一条信息都是集合的元素。审计人员通常要运用SQL查询技术，分析不同集合中哪些元素是相同的、哪些元素是不同的，以揭示审计事项的问题疑点，其本质是对两个集合或更多集合进行交运算和差运算。

左右外连接Outer join之异曲同工查询

左右外连接的基本查询语句。左右外连接需要使用 Left或 Right关键字指定包括其所有行的主表。从 from子句左边的A表中选择所有行进行分析，则使用 Left Outer join；从右边的B表中选择所有行进行分析，则使用Right Outer join。其基本语法格式为：Select……from A Left或Right Outer join B on……Where……order by……通常Outer可以省略。on子句指定两张表连接所使用的列，可以使用and、or来连接多个列。Where子句指定搜索条件。

左外连接实例及变换。左外连接将两张数据表通过Left join关键字连接起来，使用Where关键字设置一定条件，可以得到左表的行在右表中能够匹配的数据集和不能匹配的数据集。其集合运算表达式为：A∩B、A-B。

在扶贫小额贷款政策落实审计中，使用“select a.* from 小额贷款信息表 as a Left join 贫困户信息表 as b on a.借款人姓名=left（b.姓名，6）Where b.姓名 is null order by 姓名”语句，能够得到“小额贷款信息表”与“贫困户信息表”的差集，从而揭示非贫困户违规享受小额扶贫贷款政策的问题疑点。如果将Left join 前后表及相应字段互换位置，同时将Left join 改为Right join，亦能得到相同的查询效果。去掉Where子句或者用is not null，则实现的是集合交运算，能够得到兩个表中相同的数据集。

要查询贫困户没有获得小额扶贫贷款的问题疑点，将上述语句中的表名前后互换，使用右外连接也可以实现。例如，“select b.姓名，b.证件号码，a.借款人姓名 from 小额扶贫贷款信息 as a right join 贫困户信息表 as b on b.姓名=left（a.借款人姓名，6） Where a.借款人姓名 is null order by b.姓名”的查询结果包含贫困户信息表的姓名字段、证件号码字段和小额扶贫贷款信息借款人姓名字段（但为空值），反映的是贫困户没有获得小额扶贫贷款的问题疑点。

右外连接实例及变换。右外连接是将两张数据表通过Right join关键字连接起来，与左外连接恰好相反。使用Where关键字设置一定条件，能够得到右表的行在左表中匹配的数据集和不能匹配的数据集。其集合运算表达式为：B∩A、B-A，具体查询语句与左外连接实例及变换类似。如果将Right join 前后表及相应字段互换位置，同时将Right join 改为Left join，亦能得到相同的查询结果。

Except、in、exists之异曲同工查询

except查询。except是“除……之外、不包括”的意思。使用except关键字连接两个数据表，能够得到属于集合A但不属于集合B的数据集，或者属于集合B但不属于集合A的数据集。其集合运算表达式为：A-B或者B-A。求解属于集合A但不属于集合B的SQL语句为“select A.字段名 from A except（select B.字段名 from B）order by A.字段名”，置换A和B及相应字段的位置，可以求出B-A的数据集。except语句查询与使用外连接查询效果相同，但需要注意的是except语句中的所有查询必须在其目标列表中有相同数目的表达式。

in查询。in是“在……之内”的意思。使用in关键字连接两个数据表，亦能得到集合A包含在集合B的数据集，或者集合B包含在集合A的数据集。其集合表达式为：A∩B或者B∩A，依据交换律二者是等价的。使用“select 字段名1 from A where 字段名1 in（select 字段名2 from B） ”语句可以得到A∩B。较为复杂的语句为“select 借款人姓名，身份证号from dbo.小额扶贫贷款信息 where 借款人姓名 in （select 姓名 from dbo.贫困户信息表）and或or 身份证号 in（select 证件号码 from dbo.贫困户信息表） order by 借款人姓名”。若要查询集合A不在集合B的数据集合，将上述语句中的in换成not in即可，这种查询与使用左右外连接、except查询效果相同。

Exists查询。Exists是“存在、实际上有”的意思。其集合表达式和SQL语句使用方法与in 语句类似。例如，使用“select 借款人姓名 ，身份证号 from dbo.小额扶贫贷款信息 as a where not exists（select * from 贫困户信息表 as b where a.借款人姓名=b.姓名）order by 借款人姓名”，可以得到小额贷款信息表人员不在贫困户信息表人员内的元素的集合，去掉not可以查询两个表的交集，这种查询与使用左右外连接、except、in查询效果相同。

举一反三，灵活运用，异曲同工

在审计中，使用左右外连接Outer join、except、in、exists将数据集合连接起来进行分析查询，应注意做到“三会”。

一会举一反三，能够将在两表查询分析的方法推广应用到更多表的查询分析。例如，“Select * from （dbo.一季度贷款信息 as A left join dbo.三季度贷款信息 as c on A.借款人姓名=C.借款人姓名）left join dbo.二季度贷款信息 as B on C.借款人姓名=B.借款人姓名 where B.借款人姓名is null”，能够得到A、B、C三个集合的交集，去掉where子句或使用is not null，则得到的是差集。使用“select A.借款人姓名 from dbo.一季度贷款信息 as A except（select B.借款人姓名from dbo.二季度贷款信息 as B except（select C.借款人姓名 from dbo.三季度贷款信息 as C））order by A.借款人姓名”亦可以得到三个表的差集。使用“Select * from dbo.一季度贷款信息 as A，dbo.二季度贷款信息 as B，dbo.三季度贷款信息 as c where A.借款人姓名=C.借款人姓名 and C.借款人姓名=B.借款人姓名order by A.借款人姓名”语句也可以得到三个表的交集。以此类推可以得到in、exists关于三个和更多表的查询语句。

二会灵活使用，能够将查询技术灵活运用到更多类似审计事项查询分析中。例如，在土地确权与地力补贴、财政供养人员与企业登记、医疗收费与收费标准等审计事项的查询分析中，都可以使用这几种方法进行查询分析，使用时要结合数据环境灵活运用，哪种用着方便就用哪种。

三会异曲同工，能够将多种查询方法在审计事项分析中进行互相验证、比较，确保查询结果的正确性。