Quick and stupid question.. Does an INNER JOIN affect OUTER JOINed tables?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Quick and stupid question.. Does an INNER JOIN affect OUTER JOINed tables?

  1. #1
    Join Date
    Mar 2006
    Posts
    74

    Quick and stupid question.. Does an INNER JOIN affect OUTER JOINed tables?

    If i inner join to a table that has been left joined, do I flatten the outer to an inner join?

    You can assume, in the following example, that the tables all have a column called ID, and another varying-name column that is unique between tables:
    Code:
    SELECT
      id,
      a.colA,
      b.colB,
      c.colC
    FROM
      a
      LEFT OUTER JOIN
      b
      ON
        b.id = a.id
      
      INNER JOIN 
      c
      ON
        c.id = b.id
    what do I see?

    1) colA no nulls, colB nulls, colC nulls
    Total rows: count(*) of a

    2) colA, colB and colC all no nulls
    Total rows: lesser of (count(*) of b, count(*) of c)

    Cheers
    Last edited by cjard; 03-28-2007 at 05:44 AM.

  2. #2
    Join Date
    Mar 2006
    Posts
    74
    it seems to be that a left b inner c:

    a --> b <--> c

    a has 5 rows, 1,2,3,4,5
    b has 3 rows, 1,3,5
    c has 3 rows, 1,2,3


    a --> b generates:

    a1, b1
    a2, null
    a3, b3
    a4, null
    a5, b5

    b <--> c added:
    a1, b1, c1
    a3, b3, c3


    So yes, INNER JOINing a table, to a table that was outer joined, flattens the OUTERness of the join, by removing any null columns

    To work, it must be:
    a --> b --> c

Posting Permissions

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



Click Here to Expand Forum to Full Width