Tutorial by Examples

NULL is a special case when it comes to comparisons. Assume the following data. id someVal ---- 0 NULL 1 1 2 2 With a query: SELECT id FROM table WHERE someVal = 1 would return id 1 SELECT id FROM table WHERE someVal <> 1 would return id 2 SELECT id FROM tabl...

From MSDN In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. ANSI NULLS being set t...

The IsNull() function accepts two parameters, and returns the second parameter if the first one is null. Parameters: check expression. Any expression of any data type. replacement value. This is the value that would be returned if the check expression is null. The replacement value must be of a...

Since null is not a value, you can't use comparison operators with nulls. To check if a column or variable holds null, you need to use is null: DECLARE @Date date = '2016-08-03' The following statement will select the value 6, since all comparisons with null values evaluates to false or unknown...

COALESCE () Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL. DECLARE @MyInt int -- variable is null until it is set with value. DECLARE @MyInt2 int -- variable is null until it is set with value. DECLARE @MyInt3 int -...

While handling not in sub-query with null in the sub-query we need to eliminate NULLS to get your expected results create table #outertable (i int) create table #innertable (i int) insert into #outertable (i) values (1), (2),(3),(4), (5) insert into #innertable (i) values (2), (3), (null) s...