What is NULL

--What is "NULL" in SQL?

DECLARE @X INT
SET @X = NULL

IF @X = NULL
PRINT 'NULL EQUAL TO NULL'

IF @X IS NULL
PRINT 'NULL IS NULL'

--NULL means unknown.

--Will you accept two unknown is equal? i.e, NULL = NULL

--But you can check any thing is unknown? i.e, NULL is NULL

--So dont use Logical operators(=,<>,>=,<=,>,<) with NULL value

IF EXISTS(SELECT NULL)
PRINT 'UNknown Value'
/*Check this: NULL is really a valid value. But its unknown! You can assign anything to NULL
*/
--by using
SELECT ISNULL(NULL,0)

--Also, when u use some aggregate functions (sum,avg) with NULL
SELECT SUM(NULL) /*No data type for NULL; so error coming!*/
SELECT SUM(NULL+1) /* NULL -- Any value plus unknown is Unknown*/
SELECT SUM(ISNULL(NULL,0)) /*So, Always use ISNULL in aggregate functions*/

/*Small Explanation about NULL datatype*/
SELECT NULL AS 'VAL' INTO NullValue
--VAL column created as Integer! (use Alt+F1) (But i said, No data type for NULL;)
--Yes, But default data type to create table with null column is integer. But we can change it!
DROP TABLE NullValue
SELECT cast(NULL as datetime) AS 'VAL' INTO NullValue
--Now Datetime!(use Alt+F1)

IF EXISTS(SELECT 1 WHERE 1<>1)
PRINT 'Nothing!'
/*Check this: this is nothing.. nothing will return in above query!

**So Be cautious to handling about NULL and Nothing!**

*/

--Add-on Note: i have not used FROM before to WHERE clause in above query!.. is it acceptable?

DECLARE @i int
set @i =0
SELECT 'Vicky' WHERE @i=1
set @i =1
SELECT 'Vicky' WHERE @i=1

--Here we can use select statement like IF statement! :-)

/***********/