Using MYSQL JOIN always makes me go running back to the documentation. Hopefully this is enough of a reminder of the syntax.
SELECT `session_type` FROM `#__reg4_registrations` LEFT JOIN `#__reg4_sessions` ON #__reg4_sessions.id = #__reg4_registrations.sessionid WHERE #__reg4_registrations.SCNO = '{$SCNO}'