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

Beginning SQL

Hit the new query button
There is a drop down directly beneath that that by default is set to Master. We will select Northwind instead.
Make sure you are specifying the correct DB. A lazy DB developer will ruin databases. Outside of MSSMS and at the top of your query you specify it like this,
use northwind

--select everything from the products table
select *
from products

--F5 will execute the query

--Comma seperate
select productname, productID

from products

GO
USE SAMPLE
GO

CREATE TABLE tblContacts(
USE SAMPLE_DB
ID INT IDENTITY NOT NULL,
FIRSTNAME
FIRSTNAME VARCHAR(100),
LASTNAME VARCHAR(100))


If you were to continue on, and you only wanted to run this query, 
select productname, productID

from products|
you could highlight it before executing.

--Use brackets for space delimited names
SELECT [FULL NAME]
FROM CUSTOMER
--FROM<---CLAUSE keyword

select distinct CategoryID
from products
--district will eliminate duplicates

select CategoryID
from Categories

select SupplierID, CategoryID
from products
--This will generate redundant results

select distinct *
from products
--this does nothing because every line has a unique identifier

select distinct SupplierID, CategoryID
from products
--this will eliminate the redundancies

Select removes columns. Where removes rows. It is like a crosshair on a grid: Select specifies the Y-axis and Where specifies the X-axis

1. Select
2. From
3. Where

select *
FROM Products

WHERE UnitPrice > 30 AND UnitsInStock < 10


SELECT ProductName
FROM Products

ORDER BY ProductName
--alphabetical order

SELECT ProductName
FROM Products

ORDER BY ProductName desc
--Reversed alphabetical order

SELECT ProductName
FROM Products
ORDER BY ProductName asc
--this is assumed

/*If you do these, either the city or the country will be all jumbled up and out of order*/
SELECT ContactName, Country, City
FROM Customers
ORDER BY Country 
SELECT ContactName, Country, City
FROM Customers
ORDER BY Country
/*The way around situations like these is to do drilldowns*/
select ContactName, Country, City
FROM Customers

ORDER BY City, Country  
/*You probably would want the cities sub-alphabetized within the countries alphabetized, in which case you would change it to*/
select ContactName, Country, City
FROM Customers

ORDER BY Country, City