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