I’ve been trying to think of a good way to introduce how SQL Server does subqueries based on a question from a reader. One of the tricky pieces to query optimization is that the “language” used internally in the optimizer is actually somewhat more expressive than SQL. The second part of the problem is that, for people who have used other products at one time or another, it’s a challenge to get them to re-learn something to grok how SQL Server does it (and even to figure out what they need to re-learn).
So I’m going to do a series of posts on subqueries and the related pieces behind subqueries to put them in context.
Let’s start at the beginning. A subquery is a fancy filter condition where you can specify an query to run for each row of the outer query. This doesn’t mean that’s how the optimizer will actually execute it, but you can start thinking about it like that:
SELECT * FROM MyTable WHERE productkey IN (SELECT * FROM Orders where MyTable.productkey=Orders.productkey)
One way to execute this query is to read each row from MyTable, get productkey, start up another query to look through orders, and determine if we want to keep the row from MyTable based on the result of that second query. This is how some of the other QP’s on which I have worked attack subqueries and is perfectly valid.
In the internal lanaguage of the optimizer (and you can read this in output plans), you will typically see that this is executed physically in SQL Server using a “semi-join”, which you should imagine as a join operator that does what we’ve described within one single query instead of running an extra query per row. A semi-join will return rows from only one side of the join (left or right semi join). These semijoins are nice because you can actually re-use your physical join implementation to execute them. They are also nice because you can also leverage your tree rewrite logic and cardinality estimation logic to help you think about these like joins as well. So, you can reorder and rewrite the query into alternative, equivalent forms that might run more quickly.
The nice part about this approach is that you get the benefit of a lot of work and experience estimating cardinality for joins, which helps the SQL Server Optimizer make decisions about these rewrites. The bad part about this is that in queries with many joins or queries with lots and lots of data, your odds of getting a mistake in join cardinality/cost estimation go up and up, and your opportunity to get a plan that is far from optimal also go up.
My next post will cover some of these things in more detail – off to get my kid up.
Conor