The query below can be executed in any version of the AdventureWorks sample database. Don’t bother understanding the logic, there is none. It is merely constructed to show how SQL Server handles what appears to be an impossible situation.
1234 SELECT d1.Name, d2.GroupNameFROM HumanResources.Department AS d1FULL OUTER JOIN HumanResources.Department AS d2ON d2.DepartmentID > d1.DepartmentID;
If you look at the descriptions of the various join operators in the Execution Plan Reference, you will see that this query poses the optimizer for what appears to be an insolvable problem: none of the join operators can be used for this query!
But it’s possible, and Hugo explains exactly what happens, as well as places where the optimizer could be better at solving the impossible (or at least marginally difficult).