/* Using outer joins (LEFT and RIGHT JOIN) */

SELECT s.lname,  s.fname,  c.course_id
FROM   student s  LEFT JOIN  class c	        /* LEFT join means all rows from the left table */  
ON   s.ssn = c.stu_ssn;


SELECT    s.lname, s.fname, c.course_id, c.inst_ssn
FROM      student s					            /* this will return all students */  
LEFT JOIN class c					            /* even if not taking classes    */       
ON s.ssn = c.stu_ssn;		


SELECT  s.lname,  s.fname,  c.course_id 		/* similar to above		   */
FROM    student  s  LEFT  JOIN  class  c		/* outer join 			   */
    ON  s.ssn  =  c.stu_ssn				        /* but ...			       */
WHERE   c.course_id  IS NULL;				    /* only stuents not taking classes */



SELECT     i.lname,  i.fname,  c.course_id		/* this will return all instructors */ 		
FROM       class c					            /* even if not teaching classes     */ 
RIGHT JOIN instructor i			  
ON       c.inst_ssn = i.ssn;