Find if two columns exist in another table, matched as a pair-CodePudding

I've seen some other questions about comparing multiple columns, but I'm not sure they fit this exact need.

I'm trying to ensure an exact pair of columns in one table exists as the exact same pair of columns in another table. The goal is to check and mark a bit column as true false if it exists.

The last part of this script returns a 1, but I'm not sure if the logic ensures the exact pair is in the second table.

Is the logic in the last part of the script comparing both tables correct?

Sample Data:

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

CodePudding user response：

No, your query doesn't correlate the id & number so your query will return a positive if the id exists anywhere in t2 and the number exists anywhere in t2, but doesn't confirm they exist on the same row. You want EXISTS e.g.

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

CodePudding user response：

I would always opt for an exists correlation here as it's faster and NULL-safe

select
    case when exists (
      select * from #t2 t2
      where t2.courseid = t1.courseId and t2.courseNumber = t1.courseNumber
    ) then 1 else 0 end IsCourse
from #t1 t1;