Results 1 to 4 of 4
  1. #1
    Join Date
    May 2004
    Posts
    7

    Unanswered: Trouble using SELECT to retrieve NULL data from a table

    Say I have created the following tables:

    CREATE TABLE TEACHER
    (
    TEACHER_ID CHAR(4) PRIMARY KEY,
    SUBJECT_ID CHAR(4)
    );

    CREATE TABLE SUBJECT
    (
    SUBJECT_ID CHAR(4) PRIMARY KEY,
    DESCRIP VARCHAR2(20) NOT NULL
    );


    In the TEACHER table, I have the following data included:

    ('T001', 'S5');
    ('T002', 'S1');
    ('T003', 'S2');
    ('T004', NULL);
    ('T005', 'S2');
    ('T006', 'S2');
    ('T007', NULL);
    ('T008', 'S2');
    ('T009', NULL);
    ('T010', 'S2');
    ('T011', NULL);
    ('T012', 'S7');
    ('T013', NULL);


    And in the SUBJECT table, I have the following data included:

    ('S1', 'MATHS');
    ('S2', 'ENGLISH');
    ('S3', 'GEOGRAPHY');
    ('S4', 'HISTORY');
    ('S5', 'ARTS');
    ('S6', 'INFO TECH');
    ('S7', 'PHYSICAL EDU');


    I have also set up the referential integrity between these two tables:

    ALTER TABLE TEACHER
    ADD (CONSTRAINT FK_TEACHER_SUB FOREIGN KEY(SUBJECT_ID) REFERENCES SUBJECT(SUBJECT_ID));


    Just say if I want to list any of the SUBJECT_ID that contains a NULL value in the TEACHER table, how would I be able to achieve that using SELECT to query the data?

    Or another way to put it, how do I show all the subjects that do not have any teachers?

    From the data above, and given that my SELECT query is correct, I would expect something like this to show:

    SUBJECT_ID DESCRIP
    ------------ --------
    S3 GEOGRAPHY
    S4 HISTORY
    S6 INFO TECH

    This is what I did, but it showed up nothing...

    SELECT TEACHER.SUBJECT_ID, SUBJECT.DESCRIP
    FROM TEACHER, SUBJECT
    WHERE SUBJECT.SUBJECT_ID = TEACHER.SUBJECT_ID
    AND TEACHER.SUBJECT_ID IS NULL;


    It's quite easy to show up all the subjects that have teachers (by just removing the last line from the code above so it gets all data but not the ones with NULL), but why can't i do it for subjects that have no teachers?

    I just started learning SQL recently, please guide me on what went wrong?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    The SQL is wrong in 2 senses:

    SELECT TEACHER.SUBJECT_ID, SUBJECT.DESCRIP
    FROM TEACHER, SUBJECT
    WHERE SUBJECT.SUBJECT_ID = TEACHER.SUBJECT_ID
    AND TEACHER.SUBJECT_ID IS NULL;

    1) There are no SUBJECT records where SUBJECT_ID is null, so no SUBJECT record could possibly match a TEACHER record where TEACHER.SUBJECT_ID is null.

    2) Even if there were, the "=" operator will fail when either or both operands is null - i.e. even this won't work:

    SELECT *
    FROM TEACHER
    WHERE SUBJECT_ID = NULL;

    NULLs can only be compared using IS NULL and IS NOT NULL.

    There are various ways to get the subjects that have no teacher. Here is one that works in Oracle:

    SELECT SUBJECT_ID FROM SUBJECT
    MINUS
    SELECT SUBJECT_ID FROM TEACHER;

    However, some DBMSs have "EXCEPT" instead of "MINUS", and some don't support either. So here is another way:

    SELECT SUBJECT_ID FROM SUBJECT
    WHERE NOT EXISTS
    ( SELECT NULL FROM TEACHER WHERE TEACHER.SUBJECT_ID = SUBJECT.SUBJECT_ID);

  3. #3
    Join Date
    May 2004
    Posts
    7

    Question Is that relational algerba?

    Quote Originally Posted by andrewst
    SELECT SUBJECT_ID FROM SUBJECT
    MINUS
    SELECT SUBJECT_ID FROM TEACHER;
    Hi Andrewst, thank you for clearing this up for me, now I understand what went wrong. As with the quoted code above, you used MINUS, is that relational algerba to find the difference between two sets of data (find the common data and remove it)? I was thinking about doing that, but I didn't know it would work even when there are NULL values involved.
    Last edited by pkipper; 05-27-04 at 11:24.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes, it is - and it does work even if there are NULLs around (NULLs are ignored):
    Code:
    SQL> select * from t1;
    
            ID        VAL
    ---------- ----------
             1          1
             2          2
             3          3
             4
    
    SQL> select * from t2;
    
            ID        VAL
    ---------- ----------
             1
             2          2
             3          3
             4          4
    
    SQL> select val from t1
      2  minus
      3  select val from t2;
    
           VAL
    ----------
             1

Posting Permissions

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