4.10

Given the relations a(name,address,title) and b(name,address,salary), show how to express a natural full outer join b using the full outer-join operation with an on condition rather than using the natural join syntax. This can be done using the coalesce operation. Make sure that the result relation does not contain two copies of the attributes name and address and that the solution is correct even if some tuples in a and b have null values for attributes name or address.

---

SELECT COALESCE(a.name, b.name) AS name,
    COALESCE(a.address, b.address) AS address, 
    a.title,
    b.salary
FROM a FULL OUTER JOIN b 
    ON a.name = b.name AND
    a.address = b.address;