Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Posts
    139

    Unanswered: simple query question

    This is a pretty simple question, but I'm not exactly sure of this behavior. If any can explain or knows of any articles that explain this in detail I'd really appreciate it.

    The basic question is why is a null value eliminated from a result set in the following cases...

    where clause:
    col_b <> 3 does not specify to eliminate a null value in col_b but it is not included in the result set.

    from clause
    col_b <> 3 will remove the record according to the criteria but the NULL value in col_b is retained.

    If you have the patience here is the detail:

    Start with this table and results
    select
    col_a,
    col_b
    from
    tbl_a left join tbl_b on tbl_a.account = tbl_b.account
    col_a col_b
    2 3
    4 NULL

    ----------------------------------------------------------------

    Put a criteria on the WHERE clause and the NULL value is eliminated
    select
    col_a,
    col_b
    from
    tbl_a left join tbl_b on tbl_a.account = tbl_b.account
    where
    col_b <> 3

    col_a col_b
    <<-No results returned col_b = NULL eliminated
    along with col_b <> 3

    ----------------------------------------------------------------
    Put the Criteria in the FROM statement and the NULL is NOT eliminated.
    select
    col_a,
    col_b
    from
    tbl_a left join tbl_b on tbl_a.account = tbl_b.account
    and col_b <> 3

    col_a col_b
    4 NULL <<- 1 result returned NULL not eliminated
    Thanks,
    Bill

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    I am guessing that col_a is only in tbl_a, and col_b is only in tbl_b. You didn't prefix the select list with the table names so I don't know for sure. However, if that's the case, here's the reason you see a NULL for col_b:

    it's because you are using a LEFT JOIN. left join means you include all rows in the tbl_a (it's the one on the left), even when there is no match in the right table (tbl_b).

    so presumably there is an tbl_a.account that has no match in tbl_b.account when tbl_a.col_a=4. since there is no match, you get a null back for tbl_b.col_b.

    See BOL for meaning of different JOIN keywords.

Posting Permissions

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