Outer Joins

 An outer join is similar to a normal join, except that it performs special handling to prevent unjoined rows from being suppressed in the result. For example, in the join customer.customer_id = salesorder.customer_id, only customers who have sales orders appear in the result. If a customer has no sales orders, he or she is suppressed from the output. If the salesorder table is used in an outer join, however, the result will include all customers. The customer and salesorder tables will then be joined and output, as well as one row for every unjoined customer. In the query result, any reference to salesorder columns for these unjoined customers will return NULL .

POSTGRESQL 7.0 does not support outer joins. You can simulate them using subqueries and UNION  ALL, as shown in Figure .  

        SELECT name, order_id
        FROM   customer, salesorder
        WHERE  customer.customer_id = salesorder.customer_id 
        UNION ALL
        SELECT name, NULL    
        FROM   customer
        WHERE  customer.customer_id NOT IN (SELECT customer_id FROM salesorder) 
        ORDER BY name;  

In this example, the first SELECT performs a normal join of the customer and salesorder tables. The second SELECT displays customers who have no orders, with NULL appearing as their order number.