First, join TABLE_2
and TABLE_3
using an inner join and additionally filtering on Value_A = 'a'
:
SELECT
t2.ID_1,
t3.ID_A,
t3.Value_A
FROM
TABLE_2 t2
INNER JOIN TABLE_3 t3 ON t2.ID_A = t3.ID_A
WHERE
t3.Value_A = 'a'
This will give you the following result set:
ID_1 ID_A Value_A
---- ---- -------
1 b a
4 d a
Now use the above as a derived table and join it, using an outer join this time, to TABLE_1
, additionally filtering the results on Value_1 = 11
:
SELECT
t1 .ID_1,
t23.ID_A,
t1 .Value_1,
t23.Value_A
FROM
TABLE_1 t1
LEFT JOIN
(
SELECT
t2.ID_1,
t3.ID_A,
t3.Value_A
FROM
TABLE_2 t2
INNER JOIN TABLE_3 t3 ON t2.ID_A = t3.ID_A
WHERE
t3.Value_A = 'a'
) t23 ON t1.ID_1 = t23.ID_1
WHERE
t1.Value_1 = 11
;
That will give you the output you want:
ID_1 ID_A Value_1 Value_A
---- ---- ------- -------
1 b 11 a
3 NULL 11 NULL
However, nesting a query is not the only way to solve your problem – you can also use a nested join, which is much more concise:
SELECT
t1.ID_1,
t3.ID_A,
t1.Value_1,
t3.Value_A
FROM
TABLE_1 t1
LEFT JOIN
TABLE_2 t2
INNER JOIN TABLE_3 t3 ON t2.ID_A = t3.ID_A AND t3.Value_A = 'a'
ON t1.ID_1 = t2.ID_1
WHERE
t1.Value_1 = 11
;
The last query implements exactly the same logic as the previous query: first, tables TABLE_2
and TABLE_3
are joined and the result is filtered, then it is joined to TABLE_1
and the final set is filtered again. Some people also add brackets around a nested join:
FROM
TABLE_1 t1
LEFT JOIN
(
TABLE_2 t2
INNER JOIN TABLE_3 t3 ON t2.ID_A = t3.ID_A AND t3.Value_A = 'a'
)
ON t1.ID_1 = t2.ID_1
to make it clearer (perhaps both for themselves and for future maintainers) that the nested join takes place before the outer-level one, logically, although the syntax is unambiguous enough without them.
Nevertheless, many people find it confusing even with brackets, and if you find yourself struggling with it as well, there is another option – right outer join:
SELECT
t1.ID_1,
t3.ID_A,
t1.Value_1,
t3.Value_A
FROM
TABLE_2 t2
INNER JOIN TABLE_3 t3 ON t2.ID_A = t3.ID_A AND t3.Value_A = 'a'
RIGHT JOIN TABLE_1 t1 ON t1.ID_1 = t2.ID_1
WHERE
t1.Value_1 = 11
;
Again, the logical sequence of events specified by the FROM clause repeats that of the derived table solution: a join between TABLE_2
and TABLE_3
, filtered, is followed by a join with TABLE_1
. The different syntax does not alter the outcome and the results produced still match your requirements.
If ForeignId, ForeignTable, IsMain
is not known* to be unique in ExternFile
, then the QO will need to include that table to work out the count. Any time multiple rows match, the count will be affected.
Join Simplification in SQL Server
Designing for simplification (SQLBits recording)
* The optimizer does not currently recognize filtered unique indexes as unique
UPDATE (by OP):
The solution is to change line in query from LEFT JOIN (which can produce multiple rows):
LEFT JOIN ExternFile ON realty.Id = ExternFile.ForeignId AND ExternFile.IsMain = 1 AND ExternFile.ForeignTable = 5
to OUTER APPLY with TOP (which produce one row and does not affect COUNT)
OUTER APPLY (SELECT TOP (1) ServerPath FROM ExternFile WHERE ForeignId = realty.Id AND IsMain = 1 AND ForeignTable = 5) AS ExternFile
The query is now more effective. Adding a unique index could not be done, because values weren't unique, they were unique only for combination in the condition and this is not considered as unique as mentioned above.
Best Answer
The documentation is a little misleading. The DMV is a non-materialized view, and does not have a primary key as such. The underlying definitions are a little complex but a simplified definition of
sys.query_store_plan
is:Further,
sys.plan_persist_plan_merged
is also a view, though one needs to connect via the Dedicated Administrator Connection to see its definition. Again, simplified:The indexes on
sys.plan_persist_plan
are:So
plan_id
is constrained to be unique onsys.plan_persist_plan
.Now,
sys.plan_persist_plan_in_memory
is a streaming table-valued function, presenting a tabular view of data only held in internal memory structures. As such, it does not have any unique constraints.At heart, the query being executed is therefore equivalent to:
...which does not produce join elimination:
Getting right to the core of the issue, the problem is the inner query:
...clearly the left join might result in rows from
@t2
being duplicated because@t3
has no uniqueness constraint onplan_id
. Therefore, the join cannot be eliminated:To workaround this, we can explicitly tell the optimizer that we do not require any duplicate
plan_id
values:The outer join to
@t3
can now be eliminated:Applying that to the real query:
Equally, we could add
GROUP BY T.plan_id
instead of theDISTINCT
. Anyway, the optimizer can now correctly reason about theplan_id
attribute all the way down through the nested views, and eliminate both outer joins as desired:Note that making
plan_id
unique in the temporary table would not be sufficient to obtain join elimination, since it would not preclude incorrect results. We must explicitly reject duplicateplan_id
values from the final result to allow the optimizer to work its magic here.