/*
ISC 561
UNION
The UNION operator combines the results of multiple
queries into one result set. One restriction is that the
seperate relations must be the same "shape." That is
they must have the same number of columns and compatible
data types.

I use UNION for four tasks:

1. Joining multiple tables containing similar columns
into one "master" table.
2. Performing the equivalent of an OUTER JOIN.
3. Constructing a complex indicator variable

*/

/*
1. Joining multiple tables containing similar columns
into one "master" table.

The sales dept called and they want to send out a
holiday greeting card to all customers and suppliers.
They want a single mailing list that contains 
both customer and supplier addresses.

The master list should be ordered by CompanyName

The sales staff will be using mail merge and form
fields to customize each card. One of the lines in the
card text will be:

As a valued customer... OR As a valued Supplier...

We need to add an attribute to the output called CardType
that contains either the text 'Customer' or 'Supplier'

In the Northwind Database, Customer and Supplier data
are contained in two separate tables.

*/

USE Northwind

SELECT CompanyName, Address, Phone, *
FROM Customers

SELECT CompanyName, Address, Phone, *
FROM Suppliers

--Add the Mail Merge attribute "CardType"

SELECT CompanyName, Address, Phone, 'Customer' AS CardType
FROM Customers

SELECT CompanyName, Address, Phone, 'Supplier' AS CardType
FROM Suppliers

--Merge these two relations with the UNION operator

SELECT CompanyName, Address, Phone, 'Customer' AS CardType
FROM Customers
UNION
SELECT CompanyName, Address, Phone, 'Supplier' AS CardType
FROM Suppliers

/*
2. Performing the equivalent of an OUTER JOIN.

Let's revisit the first OUTER JOIN we did.
*/

/*
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, 
       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

/*
Another approach would be to create two result sets.
One for Pk=Fk matches and one for Pk<>Fk matches.
Union the results and modify the NULL value.

First Pk=Fk matches.
*/
SELECT Categories.CategoryID, CategoryName, ProductName, UnitPrice
FROM Categories, Products
WHERE Categories.CategoryID=Products.CategoryID AND
      UnitPrice > 45
ORDER BY Categories.CategoryID, ProductName

/*
Now, those that don't match.
*/

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


/*
Add in the NULL value replacements for ProductName and UnitPrice
*/
SELECT CategoryID, CategoryName, 
       'no product currently available' AS ProductName, 0 AS UnitPrice
FROM Categories
WHERE CategoryID NOT IN(SELECT CategoryID
                        FROM Products
                        WHERE UnitPrice > 45)

/*
Now put the two results together and order
*/
SELECT Categories.CategoryID, CategoryName, ProductName, UnitPrice
FROM Categories, Products
WHERE Categories.CategoryID=Products.CategoryID AND
      UnitPrice > 45
UNION
SELECT CategoryID, CategoryName, 
       'no product currently available' AS ProductName, 0 AS UnitPrice
FROM Categories
WHERE CategoryID NOT IN(SELECT CategoryID
                        FROM Products
                        WHERE UnitPrice > 45)
ORDER BY Categories.CategoryID, ProductName

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

/*
Performance?
The UNION took longer. I don't do this very often... 
I have had some very complex queries where it was easier
to find the compliment rows separately and then UNION them
to the more complex Pk=Fk results.

For example, in the CCER exam system, it was easier to return
students who hadn't yet taken the exam and UNION them onto the 
result AFTER all those elaborate calculations than make the 
calculation JOIN an OUTER JOIN.
*/

/*
3. Constructing a complex indicator variable

In the case where you want to place a row
into one of many mutually exclusive categories,
you can write a complex CASE statement in the
SELECT. But if the conditions are complex, I find
it easier to write separate queries and UNION
the results. This technique will work for non
mutually exclusive categories of course.

Let's say that Customers can be classified as:

'ARG' = ships to 'Argentina'
'OWN' = Customer contact is 'Owner' 
'AK' = Customer is in the 'AK' region 

*/
SELECT CompanyName, 'ARG' AS CustomerType
FROM Customers,Orders
WHERE Customers.CustomerID=Orders.CustomerID AND
      ShipCountry = 'Argentina'
   UNION
SELECT CompanyName, 'OWN' AS CustomerType
FROM Customers
WHERE ContactTitle = 'Owner'
   UNION
SELECT CompanyName, 'AK' AS CustomerType
FROM Customers 
WHERE Region = 'AK'


/*
How would we add a 'NA' value?
We could do an OUTER JOIN to Customers.
*/

SELECT Customers.CompanyName, 
       ISNULL(CustomerType, 'NA') AS CustomerType
FROM Customers LEFT OUTER JOIN 
     (SELECT CompanyName, 'ARG' AS CustomerType
      FROM Customers,Orders
      WHERE Customers.CustomerID=Orders.CustomerID AND
            ShipCountry = 'Argentina'
         UNION
      SELECT CompanyName, 'OWN' AS CustomerType
      FROM Customers
      WHERE ContactTitle = 'Owner'
        UNION
      SELECT CompanyName, 'AK' AS CustomerType
      FROM Customers 
      WHERE Region = 'AK') AS vwCustomerType
ON Customers.CompanyName=vwCustomerType.CompanyName
ORDER BY Customers.CompanyName