Joins When No Join Types Are Valid

Kevin Feasel

2018-08-08

Syntax

Hugo Kornelis has a brain-teaser for us:

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.

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).

Related Posts

Removing Duplicates with Window Functions

Anvesh Patel shows how to remove find duplicate rows using ROW_NUMBER() and then delete them in a statement: Recently, I got one request for one script to delete duplicate records in PostgreSQL. Most of the Database Developers have such a requirement to delete duplicate records from the Database. Like SQL Server, ROW_NUMBER() PARTITION BY is […]

Read More

Pitfalls with Window Functions

Itzik Ben-Gan takes us through two issues you might run into when using window functions: There are two common pitfalls involving window functions, both of which are the result of counterintuitive implicit defaults that are imposed by the SQL standard. One pitfall has to do with calculations of running totals where you get a window […]

Read More

Categories

August 2018
MTWTFSS
« Jul Sep »
 12345
6789101112
13141516171819
20212223242526
2728293031