/* Demo scripts for WaterOx Consulting Walk & Talk 001 - Unknown Values */

/* See how null is handled with simple math functions */
SELECT 
1+1 AS Ok,
1 + NULL AS Null_Add,
1 - NULL AS Null_Sub,
1 * NULL AS Null_Mul,
1 / NULL AS Null_Div,
1 % NULL AS Null_Mod

/* Notice how when Null is part of the equation, the end result is NULL */

/* How about if we have a left join to missing data that is returned as NULL? */
/* Start with a couple of basic tables for a customer name and their address */

CREATE TABLE dbo.Cust (CustiD INT IDENTITY, First_Name VARCHAR(15), Middle_Initial VARCHAR(1), Last_Name varchar(15))
INSERT dbo.Cust (First_Name, Middle_Initial, Last_Name)
VALUES('Chris','J','Bell'), ('Joe',NULL,'Somebody'), ('Awesome','V','Iewer')

CREATE TABLE dbo.Addr (AddressID INT IDENTITY, CustID INT, Addr1 VARCHAR(15), City VARCHAR (15), State_Code VARCHAR(2), Zip_Code VARCHAR(5), Zip4 VARCHAR(4))
INSERT dbo.ADDR (CustID, Addr1, City, State_Code, Zip_Code, Zip4)
VALUES(1,'65 Mtn Trail','P. Fred','MD','20678','1234'), (2,'123 Main St','Sometown','NY','10001',NULL)

/* Now that we have some tables, we can see how text is affected when NULL is around*/

SELECT 
CustID,
First_name + ' ' + Middle_Initial + ' ' + Last_Name AS Full_Name
FROM dbo.cust

/* How about when we perform an outer join and null is in the results set?*/
SELECT 
C.CustID,
C.First_Name,
A.Addr1,
A.City,
A.State_Code,
a.Zip_Code + '-' + a.Zip4 AS Full_Zip,
CASE WHEN a.zip4 IS NULL THEN 'Missing Zip 4' ELSE 'Zip4 Ok' END AS Zip4_Check
FROM dbo.Cust C
left JOIN dbo.Addr A ON A.CustID = C.CustID



/* When done, clean up our demo tables */

DROP TABLE dbo.Cust;
DROP TABLE dbo.Addr;