This page demonstrates use of the outer joins namely the left outer join, right outer join and the full outer join
The store manager wants a comparison of selling price and cost price of those products whose listing is present in both the 'product' table and the 'inventory' table as well as the "in house" products listing
Query the two tables using a left outer join
The result data set contains a combination of rows from the two tables with common 'product_ID'. This join eliminates information that may be present in the 'inventory' table whose product_ID s is not a match. The above SQL returns the names of products available in the store for sale, including the in house products, but not the products in the inventory table that are not in the product table, and their corresponding selling price and cost price
The store manager wants a comparison of selling price and cost price of those products whose listing is present in both the 'product' table and the 'inventory' table as well as the ones in the inventory table that are not on sale yet
Query the two tables using a right outer join
The result data set contains a combination of rows from the two tables with common 'product_ID'. This join eliminates information that may be present in the 'product' table whose product_ID s is not a match. The above SQL returns the names of products available in the store for sale, including the ones in the 'inventory' table, but not the in house products listing of the 'product' table, and their corresponding selling price and cost price
The store manager wants a complete listing of all products listed in the 'product' table and the 'inventory' table
Query the two tables using a full outer join
The result data set contains a combination of rows from the two tables with common 'product_ID'. This join does not eliminate any information that may be present in either tables whose product_ID s is not a match. The above SQL returns all records in the two tables