/*
CIS 324 

CHAPTER 7

SELECT III

NULL and IN()

*/

USE NorthWind

/*
pp. 244  IS NULL
NULL is the absence of a value, not empty string
pp. 66 interpretation of NULL
  1. An unknown value
  2. Known but missing
  3. Not applicable


*/

SELECT '' AS EmptyString

/*
The head of Sales just called. They need to send out a large mailing
this quarter and they want to "clean up" customers records that do not
contain postalcodes. Can we give them a list of customers that don't
have a postatlcode?

WHERE PostalCode = ''
WHERE PostalCode = NULL

*/
SELECT *
FROM Customers 
WHERE PostalCode = ''

SELECT *
FROM Customers 
WHERE PostalCode = NULL

SELECT *
FROM Customers 
WHERE PostalCode IS NULL


-- IS NULL, IS NOT NULL


--If we want to impose an interpreation, we use the ISNULL() function


/*
Other than listing rows that contain or don't contain NULLs,
should we deal with the interpretation at the data tier or leave
that to the application tier?

The risk for the application tier is that a develper doesn't 
catch it with exception handling and your application gets an
unhandled run time exception.

The risk for the data tier is that you make an assumption 
about the data that does not hold for all applications.


We can impose an interpretation at the data tier and 
replace NULLs with a value using ISNULL

ISNULL (check_expression, replacement_value) 

*/

SELECT ISNULL(PostalCode, 'Missing') AS PostalCode,*
FROM Customers 
WHERE PostalCode IS NULL



--To find datatype, query the sytem table with:
sp_help Customers

USE Northwind

/*
Using an enumerated list in the WHERE clause.
The IN Special Operator pp. 245

IN(enumerated list), NOT IN(enumerated list)

We will see later that we can put a query inside the IN() to create the enumerated list dynamically.

Sales called again and they want to target three postal codes in 
particular, namely, PostalCodes 1010, 05033, 44000

WHERE condition OR condition OR condition
*/

SELECT *
FROM Customers
WHERE PostalCode = '1010' OR 
      PostalCode = '05033' OR 
      PostalCode = '44000'

SELECT *
FROM Customers
WHERE PostalCode IN('1010', '05033','44000')


--WE can alternatively enumerate these values in an IN clause

SELECT *
FROM [Order Details]
WHERE Quantity = 10 OR Quantity = 40 OR Quantity = 6

SELECT *
FROM [Order Details]
WHERE Quantity IN(10,40,6)



/*
We will see later that the IN() clause is a means of formulating a
subquery.

*/