One-to-Many Joins

Up to this point, when we joined two tables, one row in the first table matched exactly one row in the second table, making the joins one-to-one joins. But what if more than one salesorder row existed for a customer ID? Multiple order numbers would be printed. In such a one-to-many join, one customer row would join to more than one salesorder row. Now, suppose no orders were made by a customer. Even though a valid customer row would exist, if there were no salesorder row for that customer identification number, no rows would be returned. We could call that situation a one-to-none join. Section  covers outer joins , which allow unjoined rows to appear in the result.

Consider the example in Figure .  

        test=> SELECT * FROM animal;
         animal_id |      name       
        -----------+-----------------
               507 | rabbit         
               508 | cat            
        (2 rows) 
         
        test=> SELECT * FROM vegetable;
         animal_id |      name       
        -----------+-----------------
               507 | lettuce        
               507 | carrot         
               507 | nut            
        (3 rows) 
         
        test=> SELECT *
        test-> FROM animal, vegetable
        test-> WHERE animal.animal_id = vegetable.animal_id;
         animal_id |      name       | animal_id |      name       
        -----------+-----------------+-----------+-----------------
               507 | rabbit          |       507 | lettuce        
               507 | rabbit          |       507 | carrot         
               507 | rabbit          |       507 | nut            
        (3 rows)  

Because the animal table's 507 rabbit row joins to three rows in the vegetable table, the rabbit row is duplicated three times in the output. This is a one-to-many join. There is no join for the 508 cat row in the vegetable table, so the 508 cat row does not appear in the output. This is an example of a one-to-none join.