Flag if there is a JOIN - SQL

Question

Two tables with same field names >> student.id in student table (a) and student.id in record table (b)

If student.id from student table is also in record table (join on a.student.id = b.student.id) then say "Yes" else "No" as "match"

Answer 1

If there are no duplicates, then you can use a left join:

select s.*, (case when r.student_id is null then 'No' else 'Yes' end)
from students s left join
     records r
     on r.student_id = s.id;

If there can be duplicates, then remove the duplicates before joining:

select s.*, (case when r.student_id is null then 'No' else 'Yes' end)
from students s left join
     (select distinct r.student_id
      from records r
     ) r
     on r.student_id = s.id;