You may recall the transitive property from elementary school math class. It states:
If A = B, and B = C, then A = C
The SQL Server optimizer can and will use this property, and it can lead to issues in your queries. When I’m writing a query, I have a clear idea of how I want it to operate. But using the transitive property, SQL Server has additional options one might not expect, and this may occasionally cause things to go awry
Click through for an example. Most of the time, the optimizer is smart about using transitivity, but sometimes it can go wrong.