AzureFeeds

In the previous T-SQL 101 post, I mentioned that some subqueries depend upon the outer query and others don’t. That’s the distinction between what are called nested and correlated subqueries.

Nested Subquery

I mentioned that you can also use a subquery to return a table of values. In the first example above, I’ve selected some columns from order lines and then said inner join to a derived table of products where their outer quantity is greater than 24.

Now again, I could have just done that directly in the outer query, but having this in a subquery could later be transformed into a common table expression (CTE) but we’ll see more about that later.

There are times when it’s just easier to write a query to find the types of products that you’re interested in, and to then treat them like a table in a more complex query. This is a simple example, but there are many scenarios where breaking down the logic of what you’re trying to achieve is helpful, rather than trying to write an entire complex query the first time.

And the advantage here is that I can just highlight the code for the subquery and execute it, to make sure that it just returns the products that I’m expecting.

When I do this, I need to have an alias for the subquery. Here I’ve chosen BulkProducts as the alias. I could have used something like bp instead, but in this case I’m spelling it out pretty clearly what it is. And then the idea is I can join the order lines table to this derived table.

Correlated Subquery

With a nested query like the one above, SQL Server can execute the query a single time and use the results.

Where that stops being the case, is if the subquery uses any columns from the outer query. You can see that in the second query above.

In this case, SQL Server needs to execute the subquery once for every row in the products table. It can’t just execute it a single time.

That’s an example of a correlated suquery.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

The post T-SQL 101: #120 Nested vs Correlated Subqueries in SQL Server T-SQL appeared first on The Bit Bucket.