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

[cjard]

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:

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

[cjard]

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