SQL Server: Why does the location of a combination change performance?

I found a performance problem and I can not understand what causes it. It has to do with the order in which the unions are made in my query. The predicates in the union are always the same, but when I move a combination from the bottom to the top of the list of combinations, it is done much faster (about 3 seconds). In contrast, when I have it in position just before the cross is applied (see the query) it is running for a long time, I left it for more than 30 seconds before stopping the execution. [I am running this on SQL server 2016 with legacy cardinality estimator]

When I compare the execution plans, I can see that it is using different indexes and doing a Hash Match in the slower version and a Nested Loop in the version that runs quickly. After doing a search, I read that the order in which the Left Unions are made does matter (although I could not find a good explanation of why my scenario was given).

Just to experiment, I changed my left union to an inner union. I still had performance differences depending on where the joint is placed. Can anyone give me an idea of ​​how / why the SQL server will change the execution plan to have a performance impact based solely on the location of a union? I had the impression that the order of the unions did not matter. Note that the object in the union that I am moving is a view that contains internal combinations, but even changing this to a table results in similar performance differences based on its location. Also, the part of the query that is being affected are NOT tables in my view or in the union predicate in which I am moving.

The union that is moving is:LEFT JOIN email.vwConvertedEmailsDelimited ON vwConvertedEmailsDelimited.ClientID = Clients.ClientID

Query A – Quick:

SELECT Clients.ClientID,
First name,
Surname,
E-mail
FROM dbo.Clients
LEFT JOIN email.vwConvertedEmailsDelimited ON vwConvertedEmailsDelimited.ClientID = Clients.ClientID
INNER JOIN dbo.MembershipContracts ON MembershipContracts.ClientID = Clients.ClientID
INNER JOIN dbo.BilledItemsMemberships ON BilledItemsMemberships.MembershipContractID = MembershipContracts.MembershipContractID
INNER JOIN dbo.BilledItems ON BilledItems.BilledItemID = BilledItemsMemberships.BilledItemID
INNER JOIN dbo.Disbursements ON Disbursements.BilledItemUID = BilledItems.BilledItemUID
INNER JOIN dbo.Receipts ON Receipts.ReceiptID = Disbursements.ReceiptID
INNER JOIN dbo.MembershipTypes ON MembershipTypes.MembershipTypeID = MembershipContracts.MembershipTypeID
CROSS APPLICATION (SELECT ABOVE (1) Effective date as the most recent date,
MembershipContractID,
StatusString,
Final date,
Observations,
Effective date
ActivePriority
FROM dbo.MembershipContractStatuses
WHERE Effective Date <= & # 39; 2019-02-28 & # 39;
AND MembershipContractStatuses.MembershipContractID = MembershipContracts.MembershipContractID
ORDER OF APPLICATION DATE
StatusPriority DESC) AS CurrentStatus
WHERE Clients.LocationID = 9
AND StatusString = & # 39; Active & # 39;

Consulta B – Slow:

SELECT Clients.ClientID,
First name,
Surname,
E-mail
FROM dbo.Clients
INNER JOIN dbo.MembershipContracts ON MembershipContracts.ClientID = Clients.ClientID
INNER JOIN dbo.BilledItemsMemberships ON BilledItemsMemberships.MembershipContractID = MembershipContracts.MembershipContractID
INNER JOIN dbo.BilledItems ON BilledItems.BilledItemID = BilledItemsMemberships.BilledItemID
INNER JOIN dbo.Disbursements ON Disbursements.BilledItemUID = BilledItems.BilledItemUID
INNER JOIN dbo.Receipts ON Receipts.ReceiptID = Disbursements.ReceiptID
INNER JOIN dbo.MembershipTypes ON MembershipTypes.MembershipTypeID = MembershipContracts.MembershipTypeID
LEFT JOIN email.vwConvertedEmailsDelimited ON vwConvertedEmailsDelimited.ClientID = Clients.ClientID
CROSS APPLICATION (SELECT ABOVE (1) Effective date as the most recent date,
MembershipContractID,
StatusString,
Final date,
Observations,
Effective date
ActivePriority - I need this to get the current contract
FROM dbo.MembershipContractStatuses
WHERE Effective Date <= & # 39; 2019-02-28 & # 39;
AND MembershipContractStatuses.MembershipContractID = MembershipContracts.MembershipContractID
ORDER OF APPLICATION DATE
StatusPriority DESC) AS CurrentStatus
WHERE Clients.LocationID = 9
AND StatusString = & # 39; Active & # 39;

Also putting the union as the last one is also fast.

Here is the fragment of the difference of the execution plan.

Execution: Approx. 3 seconds

Nested loop

Execution:> 30 seconds
HashMatch