/*
OUTER JOINS
pp. 298
--The generic syntax for OUTER joins is:
--2 tables

SELECT Table1.Attribute, Table2.Attribute, Table3.Attribute...
FROM Table1 LEFT OUTER JOIN Table2
    ON Table1.PrimaryKey=Table2.ForeignKey AND
  condition list

LEFT and RIGHT refer to the position of the table in the 
join statement, i.e., ALL rows from the table on the LEFT
of the OUTER JOIN statement

*/
USE Northwind
/*
The sales staff need a report that lists all
categories with all products in that category
that have a unitprice greater than 45. If a 
category doesn't have a product listed, print
"no product currently available" in place of
the ProductName. Put 0 for the Unitprice.

List CategoryID, CategoryName, ProductName, and UnitPrice 
for products with a unitprice greater than 45.
Order the output by CategoryID, ProductName
*/

SELECT Categories.CategoryID, CategoryName, 
       ProductName, Products.UnitPrice
FROM Categories, Products
WHERE Categories.CategoryID=Products.CategoryID AND
      UnitPrice > 45
ORDER BY Categories.CategoryID, ProductName

/*
This result shows the categories and products alright, but
what about category 2 and 5? How to include them in the
result?

The problem is, there is no corresponding FK for
rows where UnitPrice is > 45.
Specifically these two FKs.
*/

SELECT CategoryID, CategoryName
FROM Categories
WHERE CategoryID NOT IN(SELECT CategoryID
                        FROM Products
                        WHERE Products.UnitPrice > 45)

/*
The expression Categories.CategoryID=Products.CategoryID
in the WHERE clause eliminates products in category 2 and 5
and therefore there are no matching FK values when Products
is joined to Categories.
*/

/*
In order to get rows where the PK=FK AND predicate condition
was false, we have to write an OUTER JOIN
*/


SELECT Categories.CategoryID, CategoryName, 
       ProductName, Products.UnitPrice
FROM Categories LEFT OUTER JOIN Products
      ON Categories.CategoryID=Products.CategoryID
WHERE Products.UnitPrice > 45
ORDER BY Categories.CategoryID, ProductName

/*
Hmmm... that didn't work right.
My understanding is SQL Server executes the WHERE 
predicate first. Probably for performance reasons.
But this results in an unintended result.

For SQL Server, you have to put the >45 as an AND (conjunct)
to the PK=FK comparison. I think this varies by dbms...
*/

SELECT Categories.CategoryID, CategoryName, 
       ProductName, Products.UnitPrice
FROM Categories LEFT OUTER JOIN Products
      ON Categories.CategoryID=Products.CategoryID AND 
         Products.UnitPrice > 45
ORDER BY Categories.CategoryID, ProductName


/*
Replace NULL values
*/
SELECT Categories.CategoryID, 
       CategoryName, 
       ISNULL(ProductName, 'no product currently available') AS ProductName, 
       ISNULL(UnitPrice, 0.0) AS UnitPrice
FROM Categories LEFT OUTER JOIN Products
      ON Categories.CategoryID=Products.CategoryID AND 
         Products.UnitPrice > 45
ORDER BY Categories.CategoryID, ProductName

/*
We'll go over the issue of interpretation of NULL in
a later section. But the above technique is one way
of removing the potential ambiguity of NULL values.
As our author points out, NULLs can mean
1. Not appropriate
2. Not known
3. Not provided or missing (an input error)
-9999
*/
SELECT LastName
FROM Employees

SELECT *
FROM Orders
WHERE ShipCountry= 'Switzerland'
ORDER BY EmployeeID

SELECT EmployeeS.LastName, Orders.OrderID
FROM Orders RIGHT OUTER JOIN Employees
   ON Orders.EmployeeID = Employees.EmployeeID AND
      ShipCountry= 'Switzerland'

SELECT EmployeeS.LastName, 
       ISNULL(Orders.OrderID, -99) AS OrderID
FROM Orders RIGHT OUTER JOIN Employees
   ON Orders.EmployeeID = Employees.EmployeeID AND
      ShipCountry= 'Switzerland'

SELECT EmployeeS.LastName, 
  ISNULL(CAST(Orders.OrderID AS NVARCHAR), 'NA') AS OrderID
FROM Orders RIGHT OUTER JOIN Employees
   ON Orders.EmployeeID = Employees.EmployeeID AND
      ShipCountry= 'Switzerland'