-- SQL Examples for EMIS 3309
-- by Michael Hahsler

-- Cross product, inner and outer joins
-- ------------------------------------

-- This needs the product database in product_company.db

-- The basic question in this example is: "What company manufactures 
-- what product?"


-- Cross product
-- -------------

SELECT * FROM Company, Product
-- or
SELECT * FROM Company JOIN Product


-- Combines the columns of both tables and combines each row in the
-- "left" table (Company) with each row in the "right" table (Product).
-- This is the cross product. Note that the CName and the Manufacturer do
-- not always match! Remember, Manufacturer in Product is a foreign key 
-- that references CName in Company. So, they should match because it does 
-- not make sense to pair a product with a Company that does not 
-- manufacture this product.


-- (Inner) Join
-- ------------

SELECT * FROM Company, Product WHERE Company.CName = Product.Manufacturer
-- or
SELECT * FROM Company JOIN Product ON Company.CName = Product.Manufacturer
 
-- WHERE (ON) enforce that the foreign key in Product (Manufacturer) matches
-- the primary key in Company (CName). Inner Joins only retain rows that
-- have a matching entry in both tables. In our case, IBM has no product
-- in the database and thus does not show up in the result.

-- Outer Join
-- ----------

-- Maybe the fact that we have no products from IBM in the database 
-- is important. 

SELECT * FROM Company LEFT OUTER JOIN Product ON Company.CName = Product.Manufacturer

-- The left outer join preserves all rows in the table to the "left" (Company),
-- even if there is no corresponding entry in the table to the "right". Since
-- there is no data in the right, the columns are filled with NULL (see entry for
-- IBM).