/* Outer joins using old syntax in Oracle */

SELECT  s.lname,  s.fname,  c.course_id 		/* this will return all students */
FROM    student s, class c				/* even if not taking classes    */
WHERE   s.ssn  = c.stu_ssn (+);				/* (+) on the table missing rows */


SELECT  s.lname,  s.fname,  c.course_id 		/* similar to above 		   */
FROM    student s, class c				/* outer join    		   */
WHERE   s.ssn  = c.stu_ssn (+)				/* but … 			   */
  AND   c.course_id  IS NULL;				/* only stuents not taking classes */


SELECT  c.course_id, i.lname,  i.fname			/* this will return all instructors */
FROM    class c, instructor i				/* even if not teaching classes     */
WHERE   c.inst_ssn (+) = i.ssn;				/* (+) on the table missing rows    */




/* Outer joins using new syntax in Oracle */

SELECT  s.lname,  s.fname,  c.course_id 		/* this will return all students */
FROM    student s 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  c.course_id, i.lname,  i.fname			/* this will return all instructors */ 		
FROM    class c RIGHT JOIN instructor i			/* even if not teaching classes     */
  ON    c.inst_ssn = i.ssn;