时间戳

首页 > 解决方案 > 如何在需要过滤的两个表上左连接？

sql - 如何在需要过滤的两个表上左连接？

问题描述

我有两个表需要连接在一起，但两个表都需要先过滤。现在我在 SQL Server 中打开了两个表，并且都在特定列上进行了选择*，以仅给出特定结果。基本上，如果这是 Excel，我会过滤两个电子表格，然后VLOOKUP.

恐怕我的左连接会给我表 2 的所有行（50,000）的结果，而我实际上必须使用 15,000 行。

我只需要加入这些表，这样我就可以看到有多少 1:1 匹配返回到表 1。我们只是在寻找 100% 1:1 匹配。

标签： sqlsql-serverleft-join

解决方案

如果您知道首先需要 Table1 中的所有记录，那么您可以将过滤条件添加到 table2 的 JOIN 语句中，或者在派生的 table2 上进行 JOIN。

INNER JOIN 过滤器示例：

SELECT
    *
FROM Table1 AS t1
INNER JOIN Table2 AS t2
    ON t1.SupplierId = t2.SupplierId
    AND t2.Column1 = ...condition
    AND t2.Column2 = ...condition
WHERE
   ...Table1 filters

派生表上的 INNER JOIN：

SELECT
    *
FROM Table1 AS t1
INNER JOIN (
   SELECT * FROM Table2 WHERE ...Table2 filters
) AS t2
    ON t1.SupplierId = t2.SupplierId
WHERE
   ...Table1 filters

要返回 Table1 中的所有行，无论 Table2 中是否存在匹配项，您都可以改为执行 LEFT JOIN。

LEFT JOIN 过滤器示例：

SELECT
    *
FROM Table1 AS t1
LEFT JOIN Table2 AS t2
    ON t1.SupplierId = t2.SupplierId
    AND t2.Column1 = ...condition
    AND t2.Column2 = ...condition
WHERE
   ...Table1 filters

派生表上的 LEFT JOIN：

SELECT
    *
FROM Table1 AS t1
LEFT JOIN (
   SELECT * FROM Table2 WHERE ...Table2 filters
) AS t2
    ON t1.SupplierId = t2.SupplierId
WHERE
   ...Table1 filters

更新：

我只想要表 1 中的一列基本上显示匹配或 NULL。

/*
    Show all rows in [2020_supplier1] where SetID = 'ID' 
    and a count of matching records from [2020supplier2]
*/

SELECT DISTINCT
    t1.[Supplier Name],
    n.Table2Count -- will be NULL without a match.
FROM [2020_supplier1] AS t1 
OUTER APPLY (

    SELECT COUNT ( * ) AS Table2Count FROM [2020supplier2] AS t2
    WHERE
        t2.[Vendor Name] = t1.[Supplier Name]
        AND t2.[Engagement Status] = 'your condition'
        -- any additional filters...

) AS n
WHERE 
    t1.[SetID] = 'ID';

如果您不关心 [2020supplier2] 计数，则可以进一步简化此操作并节省处理时间，具体如下：

SELECT DISTINCT
    t1.[Supplier Name],
    n.VendorMatch
FROM [2020_supplier1] AS t1 
OUTER APPLY (

    SELECT CASE 
        WHEN EXISTS (

            SELECT * FROM [2020supplier2] AS t2 WHERE
                t2.[Vendor Name] = t1.[Supplier Name]
                AND t2.[Engagement Status] = 'your condition'
                -- any additional filters...
        
        ) THEN 1
        ELSE 0
    END AS VendorMatch

) AS n
WHERE 
    t1.[SetID] = 'ID';

推荐阅读