Ok, I've been seeing some weird stuff in my joins lately. And I'm confused as to exactly how joins work 'behind the scenes' so to speak.
Say I have a join like this:
join b on (a.id = b.id and a.bbb = b.bbb)
join c on (c.id = b.id and c.ccc = b.ccc)
Where a.key1 = 'whatever'
and a.key2 = 'whatever'
and a.key3 = 'whatever'
Now, here's my understanding of how this simple join would work.
At first, a result set is generated by scanning the a table. The result set would be isolated by the where clause.
So you have a result set in memory (or a table if you will).
Then you join table b to a. This is where I'm confused.
It is my understanding that when b is joined to a, the 'a' table is NOT re-queried. Rather the b table is queried and matched/joined only to the result set we have in memory already. In short, the b table is joined to the table in memory, and the first table in the query is never scanned a second time in subsequent queries. Only the existing result set in memory is joined to the following tables.
Then after b is joined to a, c is joined that that query in the same way. It is only joined to the existing record set in memory?