I have a view (definition below) which is further used in queries which run really long. I am trying to figure out wat could be the cause. The first indication is this Full join . When a query uses this view, will it perform the Full join every time? Or since the view is already created, there is not underlying full join every time the query uses this view?
What columns id this join on "Full join on 1=2" mean? Is there a way to replace the full join with an efficient way?
First, I do not understand why you would need to do FULL JOIN between same tables (dbname.table2). I am sure we can avoid that if we know what is the exact requirement. Anyways, for any reason if you want to do that, then you can JOIN them on PI of this table rather than doing 1=2. And to answer your question, yes, it would do full outer join all the time you use this view in any query. It would always perform all the joins written in the view whenever you use it.
"1=2" means a join condition which aways evaluates to false.
For an inner join this results in an empty set, but here it's a combination of both tables similar to a UNION:
The query you posted is not valid syntax, so just show the actual source code.
Wow, who wrote that?
Hopefully the AB and EF Derived Tables return only a single value, but then they should be rewritten as a Scalar Subquery (TD13+).
And the FULL JOIN ON 1=2 plus COALESCE emulates a UNION ALL of two tables with different number of columns instead of simply writing that UNION.