0

I have doubts over null values.Therefore can you please explain me briefly what is a null value in sql and can we use null values at any place in sql tables?

3
  • Strictly speaking null is not a value, it is the absence of a value. If a column is declared as not null, you have to provide a value for it. Commented Nov 1, 2018 at 17:33
  • @Lennart OK but the SQL standard refers to them as "null values". Commented Nov 1, 2018 at 19:25
  • @ybercubeᵀᴹ I don't think that implies that we should treat them as values. In that sentence I would interpret null values as a synonym for missing values or unknown values. Commented Nov 1, 2018 at 20:38

1 Answer 1

0

Allowing Null Values

Columns defined with a PRIMARY KEY constraint or IDENTITY property cannot allow null values.

The nullability of a column determines whether the rows in the table can contain a null value for that column. A null value, or NULL, is different from zero (0), blank, or a zero-length character string such as "". NULL means that no entry has been made. The presence of NULL typically implies that the value is either unknown or undefined.

Microsoft SQL Server's definition of NULL:

A value of NULL indicates that the value is unknown. A value of NULL is different from an empty or zero value. No two null values are equal. Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown.

Null values generally indicate data that is unknown, not applicable, or that the data will be added later. For example, a customer's middle initial may not be known at the time the customer places an order.

SET ANSI_NULLS setting can change the behavior of NULL. In future versions this setting will be ON by default and you will not be able to turn it off. Details here: https://learn.microsoft.com/en-us/sql/t-sql/statements/set-ansi-nulls-transact-sql

This is an article which go in depth about NULL. https://www.simple-talk.com/sql/t-sql-programming/how-to-get-nulls-horribly-wrong-in-sql-server/

Few example of NULL in TSQL here.

1
  • 3
    @I_am_feminist Note that this answer is for SQL Server, not for "SQL" - especially the part about "ANSI NULLs". Other DBMS might behave differently.
    – user1822
    Commented Apr 29, 2017 at 5:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.