Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Outer join confusion

    I've replicated a problem we came across on a smaller scale, and I was
    hoping someone could walk me through the reason why this outer join
    query is bringing back results in this manner:

    Assuming these 3 tables and their data:
    Code:
    create table emp (empnr int)
    go
    create table mail (empnr int, state varchar(2))
    go
    create table loc (empnr int, state varchar(2))
    go
    
    insert into emp  values (1)
    insert into emp  values (2)
    insert into emp  values (3)
    insert into emp  values (4)
    insert into mail values (1, 'OR')
    insert into mail values (2, 'OR')
    insert into loc  values (1, 'CA')
    insert into loc  values (2, 'OR')
    insert into loc  values (3, 'OR')
    insert into loc  values (3, 'OR')
    insert into loc  values (3, 'OR')
    insert into loc  values (3, 'OR')
    insert into loc  values (3, 'OR')
    insert into loc  values (4, 'CA')
    go
    What are the series of steps that the following query goes through in
    order to bring back its data?

    Code:
    select a.empnr as a_empnr,
           b.empnr as b_empnr,      
           b.state as b_state,
           (select DISTINCT c.state from loc c where c.empnr = a.empnr) as c_state
    from emp a, mail b
    where a.empnr *= b.empnr and
         (exists (select 1 from loc c where c.empnr = a.empnr and c.state =
    'OR')
          or b.state = 'OR')
    
    a_empnr     b_empnr     b_state c_state
    ----------- ----------- ------- -------
              1           1 OR      CA     
              2           2 OR      OR     
              3        NULL NULL    OR     
              4        NULL NULL    CA
    I thought the results were going to look like:

    Code:
    a_empnr     b_empnr     b_state c_state
    ----------- ----------- ------- -------
              1           1 OR      CA     
              2           2 OR      OR        
              3        NULL NULL    OR
    I know how to rewrite this with more EXISTS clauses, but I was wondering where my misconception lies so that I don't make this mistake in the future.

    -Chuck

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    The or makes the exists not count
    consider this
    Code:
    select a.empnr as a_empnr,
           b.empnr as b_empnr,      
           b.state as b_state,
           (select DISTINCT c.state from loc c where c.empnr = a.empnr) as c_state
    from emp a, mail b
    where a.empnr *= b.empnr and
         ( 1=2 or b.state = 'OR')
    is the same as
    Code:
    select a.empnr as a_empnr,
           b.empnr as b_empnr,      
           b.state as b_state,
           (select DISTINCT c.state from loc c where c.empnr = a.empnr) as c_state
    from emp a, mail b
    where a.empnr *= b.empnr and
          b.state = 'OR'

Posting Permissions

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