如何在SQL Server中检查记录是否存在于另一表并新增列输出结果

Table 1 #

Table 2 #

检查Table 1中的每条记录是否完整匹配存在于Table 2中，新增Available列，用Yes标识存在，No标识不存在，需逐行匹配所有列。

以下两种方法均可实现需求：

方法1：LEFT JOIN 配合 CASE 语句 #

SELECT 
    t1.*,
    CASE WHEN t2.Product IS NOT NULL THEN 'Yes' ELSE 'No' END AS Available
FROM Table1 t1
LEFT JOIN Table2 t2 
    ON t1.Product = t2.Product
    AND t1.Type = t2.Type
    AND t1.Location = t2.Location
    AND t1.`Sale Date` = t2.`Sale Date`;

方法2：EXISTS 子查询 #

SELECT 
    t1.*,
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM Table2 t2 
            WHERE t1.Product = t2.Product
                AND t1.Type = t2.Type
                AND t1.Location = t2.Location
                AND t1.`Sale Date` = t2.`Sale Date`
        ) THEN 'Yes' 
        ELSE 'No' 
    END AS Available
FROM Table1 t1;

内容的提问来源于stack exchange，提问作者JasonW