验证两列配对是否存在于另一表及SQL查询逻辑正确性

我曾查阅过多列比较的相关问题，但不确定是否契合当前需求。我需要验证表#t1中的courseid与courseNumber的精确配对是否存在于表#t2中，目标是通过位字段标记其存在状态。脚本最后部分返回1，但我不确定该逻辑是否能确保该精确配对存在于第二张表，请问这段SQL的比较逻辑是否正确？

示例数据及代码 #

CREATE TABLE #t1
 (
      courseid     VARCHAR(10)
     ,courseNumber VARCHAR(10)
 )

 INSERT INTO #t1(
    courseid
   , courseNumber
 )
 VALUES
    (3386341, 3387691)

 CREATE TABLE #t2
 (
     courseid       VARCHAR(10)
    ,courseNumber   VARCHAR(10)
    ,CourseArea     VARCHAR(10)
    ,CourseCert     VARCHAR(10)
    ,OtherCourseNum VARCHAR(10)
 )

 INSERT INTO #t2(
     courseid
   , courseNumber
   , CourseArea
   , CourseCert
   , OtherCourseNum
 )
 VALUES
    (3386341 , 3387691 , 9671 , 9671 , 233321)
   ,(3386341 , 3387691 , 9671 , 9671 , 233321)
   ,(3386342 , 3387692 , 9672 , 9672 , 233322)
   ,(3386342 , 3387692 , 9672 , 9672 , 233322)
   ,(3386343 , 3387693 , 9673 , 9673 , 233323)
   ,(3386343 , 3387693 , 9673 , 9673 , 233323)


SELECT 
   CASE WHEN courseid IN (SELECT courseid FROM #t1) AND courseNumber IN (SELECT courseNumber FROM #t2) THEN 1 ELSE 0 END AS IsCourse
FROM #t1

逻辑分析 #

你当前的SQL逻辑不正确，原因如下：

• courseid IN (SELECT courseid FROM #t1)是拿当前#t1行的courseid和#t1自身的courseid做比较，这个条件永远为真，没有实际意义；
• courseNumber IN (SELECT courseNumber FROM #t2)仅检查当前行的courseNumber是否在#t2的任意行中存在，没有和courseid做关联验证。也就是说，哪怕#t2里存在单独的某个courseid和某个courseNumber，但两者不属于同一行，这个条件也会返回真，无法确保同一行的courseid与courseNumber配对存在于#t2中。

正确写法 #

方法1：使用EXISTS子查询（推荐，性能最优） #

SELECT 
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM #t2 
            WHERE #t2.courseid = #t1.courseid 
              AND #t2.courseNumber = #t1.courseNumber
        ) THEN 1 
        ELSE 0 
    END AS IsCourse
FROM #t1

方法2：使用多列IN子查询 #

SELECT 
    CASE 
        WHEN (courseid, courseNumber) IN (
            SELECT courseid, courseNumber FROM #t2
        ) THEN 1 
        ELSE 0 
    END AS IsCourse
FROM #t1

方法3：使用LEFT JOIN结合分组 #

SELECT 
    CASE WHEN #t2.courseid IS NOT NULL THEN 1 ELSE 0 END AS IsCourse
FROM #t1
LEFT JOIN #t2 
    ON #t1.courseid = #t2.courseid 
    AND #t1.courseNumber = #t2.courseNumber
GROUP BY #t1.courseid, #t1.courseNumber

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