locked
Define NULL in Table RRS feed

  • Question

  • Trying to understand the NULL values in SQL:

    When to define a NULL field in the SQL table


    • Edited by kkran Wednesday, July 15, 2020 5:20 AM
    Wednesday, July 15, 2020 5:19 AM

All replies

  • Simple answer is - When you do not have anything to be defined, then it can be a NULL. 


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
     [Blog]  [LinkedIn]  [twitter]

    Wednesday, July 15, 2020 6:05 AM
  • NULL is unknown value.A NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation

    https://www.essentialsql.com/get-ready-to-learn-sql-server-what-is-a-null-value/


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, July 15, 2020 6:09 AM
    Answerer
  • Trying to understand the NULL values in SQL:
    See MSDN: NULL and UNKNOWN (Transact-SQL)

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, July 15, 2020 6:33 AM
  • Trying to understand the NULL values in SQL:
    When to define a NULL field in the SQL table

    That's a good question. Many people seem to make about all their columns nullable by routine, but that is a mistake. You should only make a column nullable, if you have a clear understanding of what NULL means for that particular column.

    The default interpretation of NULL is "unknown", but often there is a sharper definition of what NULL means. Say for instance in a Customers table, there is a CustRespID to hold which sales person that is responsible for this customer. Does NULL mean "we don't know who is the responsible fot his customer", or does it mean "no responsible has been assigned for this person"? Most likely, it is the latter.
    If you make a column nullable, you also need to be prepared to handle NULL in that column. If you design a customers table, and you make FullName nullable, how are you going to deal with nameless customers?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Proposed as answer by Naomi N Wednesday, July 15, 2020 2:34 PM
    Wednesday, July 15, 2020 9:35 AM
  • hi

    1- Optional fields may define null

    2- The fields frequently used in where condition and have to check is not null should avoid define null


    3-NULL is considered a variable-length value. This means that it could be a few bytes or several bytes. The database leaves room for extra bytes should the value be larger than what is stored in the field. The result is that your database might take up more hard drive storage space than if you used regular values.

    4-Database administrators also argue that if all values can’t be filled, then a record shouldn’t be created. This argument doesn’t always work for all environments, but the idea is that a record should only be created when all fields have actual values without any placeholders. For instance, you wouldn’t want to allow a bank transaction to happen if you don’t know the amount of the transaction. This standard works in the financial industry, but it doesn’t work well in other industries such as e-commerce or websites that collect user data.

     

    So Advantages also there .For more details follow the link

    https://www.doorda.com/insights/the-null-debate-should-you-use-null-values-in-your-sql-database/#:~:text=The%20Disadvantages%20of%20NULL&text=This%20means%20that%20it%20could,if%20you%20used%20regular%20values.

    https://medium.com/sappoint/the-null-debate-should-you-use-nulls-in-your-database-30e55058e517

    Thanks and regards

    Wednesday, July 15, 2020 3:49 PM
  • You've gotten some bad information on this posting. Dr. Codd created the original NULL in the relational model.  A field is not a column; a field is a part of a column, like the YEAR, MONTH and DAY in a column of data type DATE.  A NULL is not a value! It is the absence of a value. A NULL is not a zero, it is not a blank, or an empty string. Just as the creation of zero is a number centuries ago in mathematics was confusing, so is understanding the concept of a NULL.

    So much for theory. In practice, when we wrote the SQL standards, we knew the columns of the table had to be of one and only one data type. But NULLs don't have a data type in RDBMS. This doesn't work so well in a hardware implementation in SQL. This is why we have to explicitly or implicitly cast NULLs to a data type -- CAST(<expression> AS <data type>).

    Another confusion that newbies have is that a NULL is not the same as the UNKNOWN logical value. This logical value is the result of performing what are called theta operations (comparisons, in plain English) with a NULL. Officially, we have a predicate "<expression> IS [NOT] {TRUE | FALSE | UNKNOWN}" that is not in T–SQL. But wait! It gets worse. When I write search condition in the DML, I retain those rows which test TRUE. When I write a search condition in the DDL, I give the benefit of the doubt, and accept rows in a CHECK() constraint which test TRUE or UNKNOWN. Essentially we have two kinds of logic.

    Please note that I said "search condition" and not "predicate"; this is because there is no material implication operator in SQL. Without implication, technically you cannot specify a proper logic system. David McGovern is a real stickler about this. But again, in plain English, we go ahead and say predicate.

    Now that your head is hurting, Dr. Codd created two kinds of NULLs; the value is missing because the itself attribute is missing from the entity (the color of my feathers) or the value is missing because it's unknown (the color of my hair when I'm wearing a ski mask). The original NULL stood for both of these and that's what made it into SQL. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Wednesday, July 15, 2020 7:59 PM
  • Hi kkran,

    The SQL NULL is the term used to represent a missing value. A NULL value in a table is a value
    in a field that appears to be blank.
    A field with a NULL value is a field with no value. It is very important to understand that a NULL value is different than a zero value or a field that contains spaces.

    Syntax
    The basic syntax of NULL while creating a table.

    SQL> CREATE TABLE CUSTOMERS(
       ID   INT              NOT NULL,
       NAME VARCHAR (20)     NOT NULL,
       AGE  INT              NOT NULL,
       ADDRESS  CHAR (25) ,
       SALARY   DECIMAL (18, 2),       
       PRIMARY KEY (ID)
    );

    Here, NOT NULL signifies that column should always accept an explicit value of the given data type. 
    There are two columns where we did not use NOT NULL, which means these columns could be NULL.
    A field with a NULL value is the one that has been left blank during the record creation.

    Best Regards
    Echo



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by Echo Liuz Tuesday, July 21, 2020 7:54 AM
    Thursday, July 16, 2020 5:57 AM
  • Hi kkran,

    Has your problem been solved? If it is solved, please mark the point that you 
    think is correct as an answer. This can help others who encounter similar problems.

    Best Regards
    Echo


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, July 21, 2020 1:26 AM