Joins When No Join Types Are Valid

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

Implementation Matters: CTEs In Postgres And SQL Server

Brent Ozar looks at a couple of places where Postgres and SQL Server differ in implementation details: In SQL Server, if you write this query: 1 2 3 4 With AllPosts AS (SELECT * FROM StackOverflow.dbo.Posts) SELECT *   FROM AllPosts   WHERE Id = 1; SQL Server builds a query plan for the entire operation at […]

Read More

The Value Of CROSS APPLY

Kendra Little talks about one of my favorite T-SQL operators: Here’s my top 3 favorite uses for CROSS APPLY and OUTER APPLY: APPLY is fantastic for calling table valued functions. I didn’t include questions about those in the quiz, simply for the purposes of keeping the code simple, and because I wanted the quiz to […]

Read More

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Categories

August 2018
MTWTFSS
« Jul  
 12345
6789101112
13141516171819
20212223242526
2728293031