/* NULL example from http://idealprogrammer.com 
 
 PURPOSE: NULL is used in comparisions to determine if column is NULL
 
 SYNTAX: 
 
 expression IS [ NOT ] NULL
 
 
 
 
 SELECT [ ALL | DISTINCT ] 
			[TOP ( expression ) [PERCENT] [ WITH TIES ] ] 
			 column_list, ISNULL(check_expression, replacement_value)  
			[ INTO new_table ] 
			[ FROM { table_source } [ ,...n ] ] 
			[ WHERE column IS [ NOT ] NULL] 
			[ GROUP BY ] 
			[ HAVING  search_condition  ] 
			[ ORDER BY  order_expression [ ASC | DESC ] ] 
*/
 
 
CREATE TABLE people(
     ID          int,
     firstname   varchar (20),
     lastname    varchar (20),
     statecode   varchar (2),
     alive       bit,
     height      int )
 GO
 
 
 
 
 INSERT INTO people (ID, firstname, lastname, statecode, alive) --height is null 
 VALUES (1,  'Paul', 'Revere', 'AL', 0)
 GO
 
 INSERT INTO people (ID, firstname, lastname, statecode, alive, height) 
 VALUES (2,  'Pat', 'Lennon', 'NY', 0, 69)
 GO
 
 INSERT INTO people (ID, firstname, lastname, statecode, alive, height)
 VALUES (3,  'Peter', 'Martin', 'NY', 1, 75)
 GO
 
 INSERT INTO people (ID, firstname, lastname, statecode, alive, height)
 VALUES (4,  'George', 'Washington', 'VA', 0, 75)
 GO
 
 
 
 -- Select entire table
 SELECT 'Entire Table', * FROM people
 
 -- 1. Example of using IS NOT NULL
 
 SELECT 'Example #1' as Example, firstname, lastname, height 
  FROM people p
  WHERE height IS NOT NULL
 
 GO
 
 -- 2. Example of using IS NULL
 
 SELECT 'Example #2' as Example, firstname, lastname, height 
  FROM people p
  WHERE height IS NULL
 
 GO
 
 -- 3. Example showing that you should not use = with NULL
 --    Expecting 1 row but getting none 
 --    Comparison operators return UNKNOWN when either or both arguments are NULL.
 
 SELECT 'Example #3' as Example, firstname, lastname, height 
  FROM people p
  WHERE height = NULL
 
 GO
 
 -- 4. Example of how COUNT ignores NULLS
 --    4 rows in table, but COUNT(height) returns a count of 3
 
 SELECT 'Example #4' as Example, COUNT(height)
  FROM people p
 
 
 GO
 
 
 
 
 
 DROP TABLE people;
 
 GO