/* To simulate FULL JOIN in MySQL, perform a left join and another left join */
/* and UNION the 2 results */

select s.lname, s.fname, c.course_id, i.lname, i.fname
from student s
LEFT join class c
     on s.ssn = c.stu_ssn
LEFT join instructor i
     on i.ssn = c.inst_ssn

UNION

select null, null, null, i.lname, i.fname
from instructor i
LEFT join class c
     on i.ssn = c.inst_ssn
where c.course_id is null;




/* Another solution */
/* To simulate FULL JOIN in MySQL, perform a right join and a right join, but flipping the order of tables */
/* and UNION the 2 results */

select s.lname, s.fname, c.course_id, i.lname, i.fname
from instructor i
join class c
     on i.ssn = c.inst_ssn
RIGHT JOIN student s                        /* will get students not taking class */
     on s.ssn = c.stu_ssn

UNION

select s.lname, s.fname, c.course_id, i.lname, i.fname
from student s
join class c
     on s.ssn = c.stu_ssn
RIGHT JOIN instructor i                     /* will get instructors not teaching */
     on i.ssn = c.inst_ssn

order by 1 desc;