Search code examples
sqlsql-servert-sqlsubquery

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

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?

Multiple table image

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
Solution

  • 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;