I need to add value condition to the FULL OUTER JOIN.
I.e. I'm triyng to do this:
SELECT *
FROM Table1
FULL OUTER JOIN Table2 ON Table1.Field1 = Table2.Field1 AND Table2.Field2 > 5
But this script doesn't work. Actually it looks like the condition (Table2.Field2 > 5) was never applied at all.
The same issue appears for the RIGHT OUTER JOIN, so I think the reason is that when there is RIGHT or FULL join no value conditions applied to the right table in join.
Why is this happening? Is there a conceptual explanation of such behaviour?
And of course the main question is how to solve this issue.
Is there a way to solve this without using subqueries?
SELECT *
FROM Table1
FULL OUTER JOIN (SELECT * FROM Table2 WHERE Table2.Field2 > 5) AS t2 ON Table1.Field1 = t2.Field1
Pretty convulated but no subqueries
Test script
Results