Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2009
    Posts
    12

    Unanswered: Something i cant unserstand about null

    Hello everyone,

    I'm using(learning) oracle 10g for a month and firstly i'm trying to understand oracle sql..But there's something i cant understand;

    select name,surname,department_id
    from personnels where department_id != NULL;

    outcome is:no rows selected

    WHY 'no rows selected'?Only 1 department_id is null,it has to show the departments which are not null,doesn't it?

    != means not equal by the way..

    by the way i got it now if i write the same command like this;

    select name,surname,department_id
    from personnels where department_id is not NULL;

    it shows me the department_ids which are not null

    but if i put "!=" instead of "is not" the outcome is "no rows selected"..Why?What's the difference?

    Please help me..
    Last edited by dbbjk; 12-15-09 at 19:39.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    WHERE COL1 IS NULL


    WHERE COL2 IS NOT NULL


    NEVER use "=" with NULL
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2009
    Posts
    12
    Thanks for the answer

    You said 'never'?Why?

    Using it is wrong?

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Think of it like this. I am going to think of a number (null), and you have to guess what it is, or what it isn't. Sound easy? Because I am not going to tell you, if you guess right. In essence, that is how null works. No one knows what it is, or isn't, or they are not talking. Always us IS NULL and IS NOT NULL.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Using it is wrong?
    Depends upon your definition of "wrong".

    WHERE COL1 = NULL
    above ALWAYS is FALSE
    so if that is answer you desire in your SQL, then feel free to use it.

    By The way
    WHERE COL2 != NULL
    above ALWAYS is FALSE
    so if that is answer you desire in your SQL, then feel free to use it.

    also
    WHERE NULL = NULL
    above ALWAYS is FALSE
    so if that is answer you desire in your SQL, then feel free to use it.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Sep 2009
    Posts
    12
    Thanks for the nice example..Now i can think better about using null..And i'll use as you said only 'is null' or 'is not null'..Not anyother thing..

    Thanks

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    a better explanation is that a NULL is a non-value. It means that you have not yet assigned a value to the column, so there is nothing there to equate or not equate to. I, think Bonnie Baker had a good article about NULLs, she is a DB2 consultant, but she always has a clever way of explaining different concepts and most of those concepts exist in all DBMS'.
    Dave

Posting Permissions

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