Joins

InnerJoin is most common

The Customers are on

The left and the Products

are on the right. The over-

lapping purple region is where

there is some commonality. 

Most customers have ordered

some products and most 

products have been ordered.

These tables overlap where they have columns in common. Like a Ven Diagram. 



SELECT *
FROM Suppliers
SELECT *
FROM Products




--You have to join them by columns
SELECT CompanyName, ProductName
FROM Products JOIN Suppliers
ON Products.SupplierID = Suppliers.SupplierID
ORDER BY CompanyName, ProductName


Join multiple tables

/*In order to figure out which customers ordered what Product, we need to join the Customer table to the Product table, but there is no direct relation between these tables, so they need to be daisychained: the Customers to the Orders via the CustomerID, the Orders to the "Order Details" via the OrderID, and the "Order Details" to the Products via the PruductID*/
SELECT ContactName, ProductName
FROM Customers
   JOIN Orders
      ON Customers.CustomerID = Orders.CustomerID
   JOIN "Order Details"
      ON Orders.OrderID = "Order Details".OrderID
   JOIN Products
      ON "Order Details".ProductID = Products.ProductID
/*Double quotes are standard for space delimited names and would port over to Oracle and MySQL. Brackets will also work but they are T-SQL (Microsoft)specific*/
--Adding
ORDER BY ContactName, ProductName
/*Will show if anyone has ordered the same thing more than once: if they really like that product*/                                   --changing this line to
SELECT DISTINCT ContactName, ProductName
--will eliminate those duplicates 
/*******************************************/

Cross Join

/*Here we are pairing the Region table against itself. Since we are referring to the same table twice, we can make them more distinct by using the keyword AS to assign them a name for this query. AS is optional. You can say     FROM Region r1 CROSS JOIN Region r2*/

SELECT *
FROM Region AS r1 CROSS JOIN Region AS r2
ORDER BY r1.RegionDescription, r2.RegionDescription
/*There are 4 rows in this table. When the table is paired on itself, the product will result in 16 tables. This example probably wouldn't make any sense in the real world -- Unless you were trying to create an online dating service*/ 
SELECT c1.ContactName, c2.ContactName
FROM Customers c1 CROSS JOIN Customers c2
ORDER BY c1.ContactName, c2.ContactName
/*We can eliminate the redundancies by throwing in this line*/
SELECT c1.ContactName, c2.ContactName
FROM Customers c1 CROSS JOIN Customers c2
WHERE c1.ContactName < c2.ContactName
ORDER BY c1.ContactName, c2.ContactName

Outer Join