JOIN on variable number of columns I have the two tables courses AND something_about_courses: 

courses


course_code
course_session
course_type


something_about_courses


course_code
course_session
some_description


To ensure that I fetch some_description only for courses that actually exist, I use:


SELECT some_description
  FROM something_about_courses A
  JOIN courses B
    ON (A.course_code = B.course_code
   AND A.course_session = B.course_session)


Okay, everything is good and the system is put in to use. Now, two year after the system is in use, the client wants to compare not just with {course_code, course_session} but also with course_type.

So I change the structure of something_about_courses to:


course_code
course_session
course_type
some_description


and I change to query to:


SELECT some_description
  FROM something_about_courses A
  JOIN courses B
    ON (A.course_code = B.course_code
   AND A.course_session = B.course_session
   AND A.course_type = B.course_type)  &lt;&lt;&lt;&lt;&lt;&lt;&lt; AND clause added to JOIN


The problem I now face is that all old data in something_about_courses doesn't JOIN successfully since they have a NULL for course_type as it was a newly added column.

In other words, I have working queries but a disconnect in the data sets.



Solutions I have thought of:


Date based segmentation: use the old query for before the feature change and the new query for after. However, this isn't elegant and has a huge amount of maintenance overhead.
Manual adjustment of the old data: there is way too much of this to adjust it to be compatible with the new query to make this feasible.


Neither of the above solutions are ideal. I was wondering if there is anything I can do at the query level to JOIN on 2 or 3 columns based on whether something_about_courses.course_type IS NULL.
Try and see if this yields the result you want:

SELECT some_description
FROM something_about_courses A
JOIN courses B
ON (A.course_code = B.course_code
AND A.course_session = B.course_session
AND (A.course_type = B.course_type OR B.course_type IS NULL))