So far, you have used only simple joins known as inner joins or equijoins. You'll now take a look at three additional join types: the self join, the natural join, and the outer join.
Creating Self Joins
As I mentioned earlier, one of the primary reasons to use table aliases is to be able to refer to the same table more than once in a single SELECT statement. An example will demonstrate this.
Suppose you wanted to send a mailing to all the customer contacts who work for the same company for which Jim Jones works. This query requires that you first find out which company Jim Jones works for, and next which customers work for that company. The following is one way to approach this problem:
SELECT cust_id, cust_name, cust_contact FROM ...