使用SQL INTERSECT处理SQL

Question

我有两个SQL表，它们有一个基于First_Name、Last_Name和Date_Of_Birth的键。我试图编写一个简单的transact sql，以查找在Table1 Patient_Key中未在Table2中找到的异常。

表1包含152758条记录，将是每月新的数据集，表2包含8388条记录，并将继续增长

因此，我的查询需要超过1/2小时才能返回零结果(我知道，由于手动查询每个表以获得不同的Patient_Keys，它不会有任何结果)。这里的查询如下：

 SELECT T1.*
 FROM TABLE1 T1
 WHERE  upper(T1.FIRST_NAME)  + UPPER(t1.LAST_NAME) + 
 REPLACE(CONVERT(VARCHAR (10), T1.DATE_OF_BIRTH, 120), '-','') NOT IN 
 (SELECT DISTINCT upper(T2.FIRST_NAME)  + UPPER(T2.LAST_NAME) +
 REPLACE(CONVERT(VARCHAR (10), T2.DATE_OF_BIRTH, 120), '-','')
 FROM TABLE2 T2)

有没有一种更有效的SQL成本节约方法？

Answer 1

在WHERE NOT EXISTS相关子查询中，检查多个键之间存在的行要好得多：

SELECT *
FROM Table1 T1
WHERE NOT EXISTS (
        SELECT 1 
        FROM Table2 T2
        WHERE T2.FIRST_NAME = T1.FIRST_NAME
            AND T2.LAST_NAME = T1.LAST_NAME
            AND T2.DATE_OF_BIRTH = T1.DATE_OF_BIRTH
    )

如果数据库实际配置为使用区分大小写的排序规则，则应使用COLLATE选项强制进行不区分大小写的比较。效率要高得多。无论您的配置如何，都应该有一个等效的不区分大小写的排序规则。

SELECT *
FROM Table1 T1
WHERE NOT EXISTS (
        SELECT 1 
        FROM Table2 T2
        WHERE T2.FIRST_NAME = T1.FIRST_NAME COLLATE SQL_Latin1_General_CP1_CI_AS
            AND T2.LAST_NAME = T1.LAST_NAME COLLATE SQL_Latin1_General_CP1_CI_AS
            AND T2.DATE_OF_BIRTH = T1.DATE_OF_BIRTH
    )

如果您有一个关于Table1 (FIRST_NAME, LAST_NAME, DATE_OF_BIRTH)和Table2 (FIRST_NAME, LAST_NAME, DATE_OF_BIRTH)的索引，那么您应该有更好的性能。

Answer 2

为此，我建议使用left join：

 SELECT T1.*
 FROM TABLE1 T1 LEFT JOIN
      TABLE2 T2
      ON t1.first_name = t2.first_name AND
         t1.last_name = t2.last_name AND
         t1.date_of_birth = t2.date_of_birth
 WHERE t2.first_name IS NULL;

查询的问题是连接。如果您所处的环境具有区分大小写的排序规则，则应在两个单用例表中添加计算列。

对于这个查询，在table2(first_name, last_name, date_of_birth)上创建一个索引。这应该能给你所需要的表演。

Answer 3

避免转换和连接

;WITH NotInT2 AS (
    SELECT first_name, last_name, date_of_birth
    FROM   t1
    EXCEPT
    SELECT first_name, last_name, date_of_birth
    FROM   t2
)
SELECT *
FROM   t1
JOIN   NotInT2
    ON NotInT2.first_name = t1.first_name
   AND NotInT2.last_name = t1.last_name
   AND NotInT2.date_of_birth = t1.date_of_birth

此外，只有在必要时才使用UPPER()函数