Querying Two or More Tables with SQL

Caution:
- You shouldn't just do this
- ```
SELECT SKU, WarehouseID 
    FROM SKU_DATA, WAREHOUSE;
	 
```
- What did it do?
- Is this what you want? EVER?
- The entire system is based upon Relational Calculus
  - This provides a mathematical underpinning for what we are doing.
  - But we will not study that in this class.
- END RESULT: Pay attention.
What if I want to know how much inventory a buyer is responsible for?
Or what the inventory for a specific warehouse manager is?
Or even as simple as putting together an order?
In other words, what if I want to combine tables.

Subqueries

SELECT fields from table where SELECT statement

SELECT *
    FROM ORDER_ITEM
    WHERE SKU IN
        (SELECT SKU 
	     FROM SKU_DATA
	     WHERE Department="Water Sports");

This is a simple JOIN
Where we combine records from two or more tables in a database.
These can get quite complicated.
In this case, the innermost query returns all of the SKU's from the SKU_DATA table where the department is Water Sports.
How would we determine Camping's Inventory?

SELECT WarehouseID, SKU_Description, sum(QuantityOnHand) AS Here, sum(QuantityOnOrder) AS ORDERED 
    FROM INVENTORY 
    WHERE SKU IN (
        SELECT SKU 
	    FROM SKU_DATA 
	    WHERE Department="Camping") 
    GROUP BY WarehouseID, SKU_Description with ROLLUP
    HAVING ORDERED > 200;

What if we want to find the Buyers who have low inventory?

SELECT SKU
    FROM INVENTORY 
    WHERE QuantityOnHand < 100;

Now I need to look  for the Buyer by SKU
SELECT Buyer, SKU_Description
   FROM SKU_DATA 
   WHERE SKU IN (
       SELECT SKU    
           FROM INVENTORY      
	   WHERE QuantityOnHand < 100);

For this type of join, we can only return one value from the nested queries.
But we can go quite deep with the query, example on page 79

SELECT BUYER
   FROM SKU_DATA
   WHERE SKU IN (
      SELECT SKU 
         FROM ORDER_ITEM 
	 WHERE OrderNumber IN (
	      SELECT OrderNumber 
	          FROM RETAIL_ORDER 
		  WHERE OrderMonth="January" AND OrderYear=2013));

We can do much better if we do the following
- ```
Select *
   FROM SKU_DATA, ORDER_ITEM 
   WHERE SKU_DATA.SKU = ORDER_ITEM.SKU;
	 
```
- Such queries can produce duplicat fields
- ```
SELECT * 
    FROM ORDER_ITEM, RETAIL_ORDER
    WHERE RETAIL_ORDER.OrderNumber=ORDER_ITEM.OrderNumber;
	 
```
- So we might want to limit the fields
- < pre> SELECT RETAIL_ORDER.OrderNumber, SKU, Quantity, Price, ExtendedPrice, StoreNumber FROM ORDER_ITEM, RETAIL_ORDER WHERE RETAIL_ORDER.OrderNumber=ORDER_ITEM.OrderNumber;
This is called a equijoin or a inner join
This is the most common type of join.

Some more examples

SELECT Buyer, ExtendedPrice
    FROM SKU_DATA, ORDER_ITEM
    WHERE SKU_DATA.SKU = ORDER_ITEM.SKU;


SELECT Buyer, ExtendedPrice, OrderMonth
    FROM SKU_DATA, ORDER_ITEM, RETAIL_ORDER
    WHERE SKU_DATA.SKU = ORDER_ITEM.SKU 
          AND ORDER_ITEM.OrderNumber=RETAIL_ORDER.OrderNumber;