SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    What is NULL really used for?

    What is NULL really used for? I've never gotten the concept of it.
    Have a good day.

  2. #2
    SitePoint Zealot
    Join Date
    Jul 2003
    Location
    Los Angeles
    Posts
    199
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    NULL denotes an absence of a value. It's incredibly useful in many ways. Some examples.

    You want to compute the average grades on a test but some students haven't taken the test yet due to illness. If you assigned a default value of 0 to those students then using functions like AVE on all all the scores will skew the results. Instead you assign NULL as a default to those who haven't take the test yet.
    You can easily find those students by checking for IS NULL and more importantly functions like AVE, MAX, MIN all ignore NULL values.

    You have a league where members signup to play and are eventually assigned a team. You want to enforce referential integrity yet also make it possible to not belong to a team. So what happens if a member signs up in your league and isn't assigned a team right away? You can represent this with a NULL value for the team_id when you create the member sort of like this(using postgres):

    Code:
    create table teams (
        team_id SERIAL NOT NULL,
        team_name varchar(255) NOT NULL,
        PRIMARY KEY(team_id),
        UNIQUE(team_name)
    );
    
    create table members (
        member_id SERIAL NOT NULL,
        login varchar(40) NOT NULL,
        team_id int default NULL,
        PRIMARY KEY(member_id),
        UNIQUE(login),
        CONSTRAINT team_id_exists FOREIGN KEY (team_id) REFERENCES teams(team_id) ON UPDATE CASCADE
    );
    This way a member can be created and two things can happen. (1) he doesn't belong to a team yet so his team_id is set to the default NULL and we can eventually assign him a team later (2) he gets assigned to a team and through our constraint we ensure the team_id exists.

    Now it's very easy to find members who don't belong to a team with the query:
    SELECT * FROM members WHERE team_id IS NULL.

    *Note that you'd have to use INNODB tables in MySQL to enforce referential integrity.

    There are many other uses for NULL also but I must go

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    kuato++

    nice examples

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •