Filter on join condition and performance I have a query of the following type:
SELECT *
FROM CUSTOMER C
JOIN USERTABLE U on C.AccountmanagerID = US.ID

The user table also has external users which never can be the accountmanager. This table has about 30k records of which only about 500 users can be the accountmanager. I can filter them based on the length of their username. Which gives only 1k records back.
SELECT *
FROM CUSTOMER C
JOIN USERTABLE U on C.AccountmanagerID = US.ID
AND len(U.USERNAME) = 3

When i run the query plans the second one is way more efficient, which i can imagine, but I can't find any complete reasoning behind this. Can someone enlighten me and give me some background information. I am also wondering if this is always the case or is this just coincidence.
Link to the queryplan:
https://www.brentozar.com/pastetheplan/?id=HypDy3MnP
Thanks in advance
The difference is your second Execution Plan is using an Index Seek (which is typically a more performant operation) on your Customer table:

This is because your additional filter results in a cardinality that returns much less records and makes an Index Seek operation more applicable as opposed to an Index Scan. Notice the difference in thicknesses between the arrows in your first Execution Plan and the second one. There's a lot less rows being returned in the second plan because of your second query's JOIN predicate helping filter down the data upfront.
This also results in a more efficient operation being used to JOIN the two Tables together, a Nested Loops operator instead of Merge Join.
To answer your last question, this is neither a coincidence nor always guaranteed to be the case, Execution Plans and what the SQL Engine does under the hood is fairly complex, and therefor testing and comparing plans and results is the best way to understand what's going because speculating for a given scenario is difficult and varies from one scenario to the next.
In this case it just worked out that the filter you chose reduced enough records from the User table upfront, that the INNER JOIN to the Customer table was a lot smaller and allowed it to use an Index Seek operation which is more performant (and again also resulted in the JOIN operation to be something more efficient too, a Nested Loops operation).
You can see the reduction in records to JOIN to in your Customer table by looking at the &quot;Estimated Number of Rows&quot; (in this case, you should use the Actual Execution Plan instead of the Estimated Execution Plan when possible) property when you highlight over the Index Scan operation on &quot;OSUSR_6VB_MSS_CUSTOMER&quot; in the first Execution Plan vs the Index Seek operation in the second Execution Plan, went from 31,877 rows to approximately 472 rows: