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?

  • 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. – Lennart Nov 1 '18 at 17:33
  • @Lennart OK but the SQL standard refers to them as "null values". – ypercubeᵀᴹ Nov 1 '18 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. – Lennart Nov 1 '18 at 20:38

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://docs.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.

| improve this answer | |
  • 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. – a_horse_with_no_name Apr 29 '17 at 5:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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