Execution Order of Nested Joins

[Harry Shmedlap]

It seems to me that one join-related failure in an Access 2003 query is when one of the fields involved in the join has null values. One way this happens is if an outer join is followed by an inner join. For example "F LEFT JOIN G" will produce full field values for both F and G fields, only in records where there is a match between the joined fields. If there are any additional records in the F table (due to the LEFT join), then only the fields of F will be included in the output, while all the G field values will necessarily be null. If this is followed by an inner join on H, where the join is done in one of G's fields then, these null values will cause this inner join to fail. Alternatively if the first join was "F RIGHT JOIN G" then, since this time all of G's values will be included, then the subsequent inner join with H should not fail.

Such is my understanding. However I don't understand the Access Help statement, under the topic of "LEFT JOIN, RIGHT JOIN Operations", that seems to say the opposite:

NOTE: A LEFT JOIN or a RIGHT JOIN can be nested inside an INNER JOIN, but an INNER JOIN cannot be nested inside a LEFT JOIN or a RIGHT JOIN.

Furthermore the key here seems to be execution order. What determines whether the outer join or the inner join execute first. I thought that brackets should clearly resolve that but why does the following query fail:

SELECT *
FROM (F INNER JOIN H ON F.f1=H.h1) RIGHT JOIN G ON G.g3=F.f2;

Here the brackets should make the inner join get executed first, yielding all "good" fields, so that there should be no failure in the RIGHT JOIN. Yet it indeed fails. Why?

Furthermore if I change the above to a LEFT join, then it no longer fails.

 

[namliam]

Right join = Right join while Left join = Left join

Lets define them
table1
1
2
4

Table2
2
3
4

Inner Join of above 2 tables
2 2
4 4

Table1 Left join Table2
1
2 2
4 4

Table1 Right join Table2
2 2
3
4 4

Table2 Left join Table1 ( = Table1 Right join Table2)
2 2
3
4 4

Table2 Right join Table1 ( = Table1 Left join Table2)
1
2 2
4 4


The difference is this:

F = H <= G Right join
Meaning Fetch all data from G and join any information you can find in the "F = H" Join, this is not allowed as you are nesting the Inner join in the Right outer join.
F = H => G Left join
Meaning fetch all date from the "F = H" join and add any date you can find in G. This is allowed.

I hope I was clear enough? If not, post any questions and I will try and answer them.

 

[Harry Shmedlap]

Thanks for the response
The examples are clear but the explanation at the end isn't.

1) Its not clear by what you mean by "F = H <= G Right join". Is this some kind of database "algebra"? What does it mean, in words?

2) In your explanation it seems like you are doing the same thing in both cases: gathering together two separate recorsets (G and F=H) and joining them.

3) What does it matter which join is nested? So long as the inner join gets executed first, there are no nulls, so why does the left join fail?

 

[namliam]

1)
= would simbolize a inner join
=> or <= would simbolize the Left and right joins.

Not really "general database algebra" just thought it worked.

2)
Yes the examples are the same thing. Just the "exact technical solution" is different.
A Right join B is exactly the same as B Left join A
That is the point I am trying to make.

3)
Because it is not a matter of beeing executed first... You cannot execute any join first...
All joins are per default allways executed at the same time in the DB, brackets have no meaning towards executing it first.

A Left join B
Means Get all info from A and allow nulls from B

A right join B
Means get all Info from B and allow Nulls from A

we add
C join A
This demands that there are records in A, therefor A cannot contain nulls, therefor the one fails and the other doesnt.

I hope this is a little more clear?

 

[Harry Shmedlap]

Thanks for your interesting response. Here are some followup questions:

If "brackets have no meaning towards executing it first" then:
3.1) Why are brackets used when there are two or more joins? I have found that the query just won't work unless I have at least one set of brackets. Furthermore, the Access Help shows that multiple joins must have brackets.

3.2) So I go back to my original question: Why the requirement:
A LEFT JOIN or a RIGHT JOIN can be nested inside an INNER JOIN, but an INNER JOIN cannot be nested inside a LEFT JOIN or a RIGHT JOIN.

 

[namliam]

3.1 because in multiple inner and outer joins stuff can get really complex. But in the basics brackets do not mean anything... They are mainly there for logic and reason of the human.
In very very few occations in the where clause for example it will actually be able to mean something, in even fewer in the from or having. But basicaly Nada...

3.2
Becuase... because you cannot.

Inner join requires that the two tables have matching records, where outerjoins (lef or right) allow for "null" or non-matching records on one side or the other.

So a query of:
(1) A => B = C ( A Left join B join C )
would query records from A, allow Nulls from B but C will require data present in B => Confilcting joins

While
(2) A <= B = C ( A Right join B join C )
requires Data in A and B and allows null in A => No problems

You can make 1 work by making this query:
A => B => C (A left join B left join C)
That way you allways allow blanks in B and C as subset of A, resolving the conflict.

** Disclaimer I am allways mixing up Left and right joins, I hope I used them properly.
** The => and <= symobilize the left and right join again, where = is a join.

 

[Harry Shmedlap]

I still don't see what determines which query is the nested one? Especially if you say that both take place at the same time? Perhaps I don't understand the term "nested" in this context.

 

[namliam]

Nested in this context is hard... I guess so, as it is not nesting.... like nesting queries...

Maybe something like
Left - Inner - Right

Would make it more logical? As long as you keep it like that you should be OK....