4.3

Outer join expressions can be computed in SQL without using the SQL outer join operation. To illustrate this fact, show how to rewrite each of the following SQL queries without using the outer join expression.

a.

SELECT * FROM student NATURAL LEFT OUTER JOIN takes
SELECT * FROM student NATURAL FULL OUTER JOIN takes
SELECT * FROM student NATURAL LEFT OUTER JOIN takes

can be rewritten as:

SELECT * FROM student NATURAL JOIN takes
UNION
SELECT ID,name,dept_name,tot_cred,null,null,null,null,null
FROM student S1 
WHERE NOT EXISTS (SELECT ID FROM takes T1 WHERE T1.id = S1.id)

or

SELECT * FROM student NATURAL JOIN takes
UNION
SELECT ID,name,dept_name,tot_cred,null,null,null,null,null
FROM student S1 
WHERE ID NOT IN (SELECT ID FROM takes)
SELECT * FROM student NATURAL FULL OUTER JOIN takes

can be rewritten as:

(SELECT * FROM student NATURAL JOIN takes)

UNION

(
    SELECT ID,name,dept_name,tot_cred,null,null,null,null,null
    FROM student S1    
    WHERE NOT EXISTS (SELECT ID FROM takes T1 WHERE T1.id = S1.id)
)

UNION 

(
    SELECT ID,null,null,null,course_id,sec_id,semester,year,grade
    FROM takes T1
    WHERE NOT EXISTS (SELECT ID FROM student S1 WHERE T1.id = S1.id)
)