unusual sql server query result

This is the intended behavior. You cannot compare NULL values using = or <>. You have to use IS NULL or IS NOT NULL.

If you want NULL values only use IS NULL:

select * from nameAge where age IS NULL

If you want NULL values with age <> 12 values, use:

select * from nameAge where age <> 12 OR age IS NULL

The expression

WHERE NULL <> 12

does not return TRUE or FALSE, but actually returns UNKNOWN. This means that the third record in your table will not be returned by your query.

As @ughai mentioned, you should use IS NULL instead to query that record:

SELECT * FROM nameAge WHERE age IS NULL

Have a look at the Microsoft SQL Server documentation for more information.

When you are dealing with NULLs you should be always careful because of 3 valued logic used in Sql Server(when a predicate can be evaluated to TRUE, FALSE or UNKNOWN). Now here is a classic select statement where many newcomers make a mistake, suggesting that the statement would return all rows where Age <> 12 including NULLs.

But if you know the easy fact that comparing NULL to any value, even to NULL itself will evaluate to UNKNOWN it is getting more clear what is going on. WHERE clause will return ONLY those rows where predicate is evaluated to TRUE. Rows where predicate evaluates to FALSE or UNKNOWN will be filtered out from resultset.

Now let's see what is going on behind the scene. You have 4 rows:

ID    Name    Age1     X       122     Y       123     null    null4     Z       12

and the predicate is:

where Age <> 12

When you evaluate this predicate for each row you get:

ID    Name    Age   Evaluation result1     X       12    FALSE              --(because 12 <> 12 is FALSE)2     Y       12    FALSE              --(because 12 <> 12 is FALSE)3     null    null  UNKNOWN            --(because NULL <> 12 is UNKNOWN)4     Z       12    FALSE              --(because 12 <> 12 is FALSE)

Now remember that WHERE clause will return only rows where predicate evaluates to TRUE and it is clear that you will not get any result because no row evaluates to TRUE.