I apologize for the long question in advance. Most online articles don't go over this, they just show a quick result set. For such an important and commonly used idea, I want to fully understand this. I've seen a lot of the post on here with specific examples, but none got the core idea in my head. My question is when you do a 3+ table join, how does this work in memory? The statement I'm currently using is:
select a.cust_id, a.[first name],a.[last name],a.[primary zip],c.jerseynum
from contact as a
join notes as b
on a.cust_id = b.cust_id
join jerseytable as c
on a.cust_id = c.cust_id
so after the first join between a and b we get a result set, we'll call it 1 I then do a join on a and c... this is were it gets fuzzy for me. This result set doesn't just take the place of my previous join, does it only add records to 1 that fit just the join between a and c?
a joined with b, then the result set is joined with c. (If you use MS SQL Server, you can see this process in query execution plan).