Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2008
    Posts
    7

    Question Unanswered: left joins and column nulls

    Hi,
    I have been shown a technique of using left joins on two tables and then using one of the table's columns and test if for nulls. I am not really understanding why or how it works. Can anyone explain or direct me to a book or reading material that explains this concept?
    For example.
    Select t1.col1, t2.col1 from table1 as t1
    left join table2 as t2 on t1.idcol=t2.idcol
    where t2.col1="something" and t2.idcol is null.

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    A LEFT JOIN operation returns all of the rows in "left" or first table. It will return rows from the "right" or second table if the matching condition in the ON clause is met, or if there are no matching rows in the "right" table there will be one row returned from the "left" table with all NULL values for the columns from the "right" table.

    By checking a column from the "right" table that is in the ON clause for a NULL value, the result set will only return rows from the "left" table that have no matching row in the "right" table.

    -PatP

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Read the cookbook located at:

    DB2 SQL Cookbook

    Andy

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by ofilha

    Select t1.col1, t2.col1 from table1 as t1
    left join table2 as t2 on t1.idcol=t2.idcol
    where t2.col1="something" and t2.idcol is null.
    I don't think this query will return anything at all, because t2.col1 will be null in every case when t2.idcol is null, so the predicate t2.col1='something' will always be false.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Sep 2008
    Posts
    7

    Smile

    Thank you all for the helpful hints. I could not get my head wrapped around this concept and your ideas have helped to clarify it.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by n_i
    I don't think this query will return anything at all, because t2.col1 will be null in every case when t2.idcol is null, so the predicate t2.col1='something' will always be false.
    I applied a bit of "creative reading" skills and tried to understand what ofilha meant as opposed to what they literally posted. If we assume that the t2.col1 was really meant to be another abstract condition in the WHERE clause, then the whole thing makes sense. As you pointed out, if the sample SQL is read literally and t2.colid actually is NULL, then all of the columns in t2 will be NULL.

    -PatP

  7. #7
    Join Date
    Sep 2008
    Posts
    7

    Lightbulb

    Yes, i am glad you applied your creative reading skills rather than a literal reading of my post. Since the issue is confusing to me my question was likely confused, but the answer Pat gave is what i was looking for. I just needed an explanation of the usage of the is null with this kind of query.

  8. #8
    Join Date
    Sep 2008
    Posts
    7
    Just so i understant it better.
    Now if the example i posted was changed to:

    Select t1.col1, t2.col1 from table1 as t1
    left join table2 as t2 on t1.idcol=t2.idcol
    where t1.col1="something" and t2.idcol is null.

    The result set would then would bring all the rows from t1 that do not match t2.idcol but match "something" in t1.col1 ? Am i going in the right direction?

    And thanks you all for the help.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yes, your revised query would return the result test rows that had a value of "something" in t1.col1 where there was no row in t2 which made the ON condition t1.idcol = t2.idcol. It is important to note that if t1.idcol is NULL, it doesn't matter if there are any rows with NULL in t2.colid or not for two different reasons...

    Most importantly, NULL is by definition not comparable so it is never "equal to" anything, not even another NULL (which is why you must use the "IS NULL" predicate instead of using = NULL in its place). Even if that weren't the case, the value would still be NULL if the join succeeded, so the code wouldn't be able to tell any difference!

    -PatP

Posting Permissions

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