Want to understand a query for a view i'm trying to dissect

Question

I'm confused by a bit of a query i'm working with.

select *
from Table1
inner join Table2
  on Table1.id1 = Table2.id1
right outer join Table3
right outer join Table4
inner join Table5
  on Table4.id1 = Table5.id1
  on Table3.id1 = Table5.id2
  on Table1.id2 = Table5.id3

I tried to keep the query as close to what i'm working with as I could.

I don't understand the joins without the ON and then the join with multiple ONs. Are tables 3 and 4 not actually being joined until after table 5 is joined?

The following doesn't work as Table5.id1 and Table5.id2 receive 'multi-part identifier "Table5.id_" could not be bound

select *
from Table1
inner join Table2
  on Table1.id1 = Table2.id1
right outer join Table3
  on Table3.id1 = Table5.id2
right outer join Table4
  on Table4.id1 = Table5.id1
inner join Table5
  on Table1.id2 = Table5.id3

Additionally, this bit does process because table 5 is joined first and solves the bounding error, but I receive about 27k more records than is wanted

select *
from Table1
inner join Table2
  on Table1.id1 = Table2.id1
inner join Table5
  on Table1.id2 = Table5.id3
right outer join Table3
  on Table3.id1 = Table5.id2
right outer join Table4
  on Table4.id1 = Table5.id1

So at this point it's obvious the original query is built the way it is for a reason, but I still don't understand the logic behind it or what is actually happening.

Any help would be much appreciated.

Answer 1

What you have here are multiple nested joins. Before I explain, I'm going to reformat the query a bit to make it easier to see what's going on.

select *
from Table1
inner join Table2 on Table1.id1 = Table2.id1
right outer join Table3 -- Join B
    right outer join Table4 -- Join A
        inner join Table5 on Table4.id1 = Table5.id1
    on Table3.id1 = Table5.id2 -- ON clause for Join A
on Table1.id2 = Table5.id3 -- ON clause for Join B

Nested joins let you join two tables together then join that result to another set of records. Initially that doesn't sound terribly useful. That's just a regular join, right? Kinda. The difference is that only if the inner-most join succeeds does it attempt to join that row to the outer table. This isn't really useful at all if all you are doing is using inner joins. It becomes a lot more interesting if you are mixing inner and outer joins (more on that shortly).

I'll attempt to explain what's going on with this query, both in prose and in comments so hopefully between the two it will make sense.

First, the inner-most join here is an inner join between tables 4 and 5. Those tables are joined together first. That will give you a result set where each row in Table4 has at least one matching row in Table5 (according to whatever criteria exists in the on clause, in this case that Table4.id1 = Table5.id1). This implicitly filters out any rows from both Table4 and Table5 that don't have a match in the other table.

Then that result is then right joined to Table3 (on Table3.id1 = Table5.id2). Meaning you will get all records from Table3 joined with their corresponding match in the Table4/5 join set (if present).

Then we do a right join on that whole result set with Table1 (on Table3.id1 = Table5.id2). Meaning we will end up with everything in Table3 joined to the Table4/5 combo and then to a Table 1/2 combo.

The ultimate result set is everything from Table3 joined with 0 or more rows that match with Table1 and Table2 (if Table1 doesn't have a matching Table2 record, neither will be joined to Table3). Same for Table4/5. I believe this is correct (too much staring at this without the ability to run the query means I may have confused myself, but the basic idea is correct).

So why this crazy syntax? Alternatives are kind of a pain too. You could use CTEs or apply statements, both of which are their own kind of fun (not necessarily hard, just not your vanilla SQL. I tried converting your query using those and I think I got reasonably close, then I confused myself into a corner because of poor naming of things then I gave up). So why do this? Well it means you can ensure that you can outer join two tables to a third table only if there are matches in the first two tables. Maybe a more concrete example would help?

Say you have 4 tables Person, Order, OrderItem and OrderItemDiscount. You are tasked with getting back a result set that shows every order and to highlight orders that contain a Figlewubbit and where a discount code was used on it. So you write this:

select *
from Person p
left join Order o on o.PersonId = p.PersonId
left join OrderItem oi on oi.OrderId = o.OrderId
                          and oi.ItemName = 'Figlewubbit'
left join OrderItemDiscount oid on oid.OrderItemId = oi.OrderItemId

Another way to write it would be this:

select *
from Person p
left join Order o on o.PersonId = p.PersonId
left join OrderItem oi 
    inner join OrderItemDiscount oid on oid.OrderItemId = oi.OrderItemId
on oi.OrderId = o.OrderId
    and oi.ItemName = 'Figlewubbit'

The execution plan here will change. OrderItem and OrderItemdDiscount will get joined together then that set will get fed into the left join to Order. Each OrderItem and OrderItemdDiscount joined row is effectively treated as a combined entity for the other joins. You won't get one without the other.

(I apologize if this example seems contrived. Nested joins are a weird beast. They have their uses (I've needed them once or twice). But coming up with a simple example that requires their use is quite hard. They are a very specialized tool that usually requires an equally specialized (and complicated) requirement to warrant their use. I highly recommend researching this some more and using simple versions of them first. Combining right joins and multiple nested joins even gives me a headache trying to parse it.)

Answer 2

Actually, tables 4 and 5 are joined first, then table 3 is joined. Here's execution plan: