This past week, I was sick and so I didn’t hang around social media much. But as I got better, I found more and more of the types of posts that just scream “I don’t know what I am talking about” from the poster. This was a pretty easy topic for the day, so I figured why not.

I will also note that I usually don’t start turning this into a blog until I have learned something “interesting”. In this case, I was sure that the initial supposition was correct, but rather because of a tidbit I learned in Taking it further section

The Pattern

The post states that a query such as:

SELECT soh.SalesOrderID,
       (SELECT  C.AccountNumber
        FROM    Sales.Customer AS C
        WHERE   C.CustomerID = SOH.CustomerID) AS CustomerAccountNumber
FROM   Sales.SalesOrderHeader SOH;

will, by definition, execute that subquery on the Customer object one time per row in the SalesOrderHeader table.

But is this true? First, let me state, in this particular case, the better way to do this IS with a JOIN. Sometimes though, a subquery is a nice way to output a calculation in the SELECT clause. But should you work really hard to fix this query because an optimizer can’t see what you can see… that this would be better as a JOIN?

The test

So we have these two queries:

--the original terrible query
SELECT soh.SalesOrderID,
       (SELECT  C.AccountNumber
        FROM    Sales.Customer AS C
        WHERE   C.CustomerID = SOH.CustomerID) AS CustomerAccountNumber
FROM   Sales.SalesOrderHeader SOH
--the preferred version
SELECT soh.SalesOrderID,
       C.AccountNumber as CustomerAccountNumber
FROM   Sales.SalesOrderHeader SOH
          JOIN Sales.Customer AS C
            ON C.CustomerID = SOH.CustomerID

Take it as done that I have used the techniques to compare sets of data I previously posted about to make 100% sure that the results are identical. But what about the query stats and plans? Starting with the original query, I use these calls to get a plan and query stats:

--the original terrible query
SELECT soh.SalesOrderID,
       (SELECT  C.AccountNumber
        FROM    Sales.Customer AS C
        WHERE   C.CustomerID = SOH.CustomerID) AS CustomerAccountNumber
FROM   Sales.SalesOrderHeader SOH;

Here is the query plan:

Now let’s get the statistics:

SET STATISTICS IO ON;
GO
--the original terrible query
SELECT soh.SalesOrderID,
       (SELECT C.AccountNumber
        FROM Sales.Customer AS C
        WHERE C.CustomerID = SOH.CustomerID) AS CustomerAccountNumber
FROM Sales.SalesOrderHeader SOH
GO
SET STATISTICS IO OFF;
GO

This outputs (just the logical reads unless some other values are non-zero):

Table 'SalesOrderHeader'. Scan count 1, logical reads 57
Table 'Customer'. Scan count 1, logical reads 123

Now, just from these stats, you should know the myth is busted, because there are just 1 scan per table. If this was actually doing a row per SalesOrderHeader this would be a lot larger (31465), or even if it was doing a scan per CustomerId, it would still be > 1 since if you look at the output, there is more than 1 CustomerAccountNumber output.

Now do the same for the JOIN version:

--the preferred version
SELECT soh.SalesOrderID,
       C.AccountNumber as CustomerAccountNumber
FROM   Sales.SalesOrderHeader SOH
          JOIN Sales.Customer AS C
            ON C.CustomerID = SOH.CustomerID;

Looking at the graphical plan

you can see they look the same, except for an extra compute scalar value.

Grab the query stats and you will see the same IO. So that’s it right? Not so fast.

The Recommendation

The original supposition that the query would run multiple times is actually not universally true. The SQL optimizer realized this query was basically the same as the join and optimized as such. However, it’s still recommended to avoid correlated subqueries when possible, as they can sometimes lead to less efficient execution plans in more complex scenarios. Using JOINs generally provides better performance and readability.

But never forget that the previous paragraph says “when possible”, not because it will take over 32000 times longer to execute the previous query, but because it is just a better practice to start with that will usually be the same performance or faster.

But I am not finished

Of course, because as much as I do like writing to read myself talk, I am far more interested in learning something. So I wondered…what if there were two correlated subqueries that pulled different data from the same table? I am pretty glad I kept going.

--the original terrible query
SET STATISTICS IO ON
SELECT soh.SalesOrderID,
       (SELECT C.AccountNumber
        FROM Sales.Customer AS C
        WHERE C.CustomerID = SOH.CustomerID) AS CustomerAccountNumber,
      (SELECT C.PersonID
       FROM Sales.Customer AS C
       WHERE C.CustomerID = SOH.CustomerID) AS CustomerPersonId
FROM Sales.SalesOrderHeader SOH
--the preferred version
SELECT soh.SalesOrderID,
        C.AccountNumber as CustomerAccountNumber,
        C.PersonID
FROM Sales.SalesOrderHeader SOH
JOIN Sales.Customer AS C
ON C.CustomerID = SOH.CustomerID

So I added another subquery, and then the “best” way to do this sort of thing. –now, these query plans are different, in the way you might already suspect.

The query plan now shows that the subqueries have been turned into a join, but now there are 2 of them. As you will see in the statistics IO.

Table 'SalesOrderHeader'. Scan count 1, logical reads 57
Table 'Customer'. Scan count 2, logical reads 246

Now there are 2 scans on the Customer table, instead of 1 as you can see in the output for the JOIN version.

Table 'SalesOrderHeader'. Scan count 1, logical reads 57
Table 'Customer'. Scan count 1, logical reads 123

In essence, having two subqueries is almost like you wrote:

SELECT soh.SalesOrderID,
       C.AccountNumber as CustomerAccountNumber,
      C2.PersonID
FROM Sales.SalesOrderHeader SOH
      JOIN Sales.Customer AS C
         ON C.CustomerID = SOH.CustomerID
      JOIN Sales.Customer AS C2
         ON C2.CustomerID = SOH.CustomerID;

Which if you look at the statistics IO output, you’ll see that it’s essentially the same as the two correlated subqueries version.

Table 'SalesOrderHeader'. Scan count 1, logical reads 57
Table 'Customer'. Scan count 2, logical reads 246

But your customer table had an index

You might think that this could mean something. I will be honest, when I first tried this I didn’t know what would happen. I was actually a bit surprised.

So I will threw the entire Customer table into a heap:

SELECT *
INTO   tempdb.dbo.hold
FROM   Sales.Customer;
SELECT soh.SalesOrderID,
       (SELECT  C.AccountNumber
        FROM    tempdb.dbo.hold as C
        WHERE   C.CustomerID = SOH.CustomerID) AS CustomerAccountNumber
FROM   Sales.SalesOrderHeader SOH;

Run this with SET STATISTICS IO ON; and you will see something like this:

Table 'Worktable'. Scan count 31465, logical reads 103252
Table 'SalesOrderHeader'. Scan count 13, logical reads 166
Table 'hold'. Scan count 1, logical reads 155

This time you might think it scanned the entire table, but that Worktable is telling us something else. And why did we scan SalesOrderHeader 13 times? I am not actually sure. Let’s check out the plan in any case:

Looking at the plan, the biggest cost is something called an Index Spool. I will let you read Hugo’s post on this if you want to dig into this, but the long of it was that an index was created on the heap, and it did have to probe that index once for each row in the table.

But what if it was a join? Would this be as bad?

SELECT soh.SalesOrderID,
       C.AccountNumber as CustomerAccountNumber
FROM   Sales.SalesOrderHeader SOH
          JOIN tempdb.dbo.hold as C
            ON C.CustomerID = SOH.CustomerID;

Well, this turns out quite a bit better of an operation (quite):

And you can see the rows needed are different.

Table 'Workfile'. Scan count 0, logical reads 0
Table 'Worktable'. Scan count 0, logical reads 0
Table 'SalesOrderHeader'. Scan count 1, logical reads 57
Table 'hold'. Scan count 1, logical reads 155

Turns out in this case, it just used a hash join between the two objects. It definitely means that the query wasn’t internally rewritten to be the same thing in the subquery case, even if we can see that it is the same output.

This, my friend, is interesting. It wouldn’t change my advice in any way shape, or form, but it is interesting. My advice is always 100%:

• get the right answer
• speed up your query as needed
• but always get the right answer

If you can’t get the right answer without a subquery in the SELECT clause, use it. If you can make it a JOIN, do that. Whatever is the best way to get the right answer, with performance coming second.

General advice

Of course, as I have heavily noted, don’t use a subquery when a JOIN will suffice. This is good information whether or not it works faster. It just is way more clear to the reader. But subqueries get a bad wrap, and even in SQL Server, were the heap meant more work, it didn’t go row by row and scan the entire table over and over.

This whole thing goes heavy into the talking across purposes that happens on this “post generic advice about SQL” forum that people have been treating LinkedIn as. SQL is mostly SQL. But Data Platforms are no where near one another. I have really started seeing the light about this as I work with Microsoft Fabric Data Warehouses. I am using T-SQL, and so much is the same. But when there are no indexes, the way things work are different.

So when you are working with a more relational engine like SQL Server, PostgreSQL, MySQL etc, you need to think about query tuning in one way because you are using a type of engine built initially for single row fetches and modifications. They have evolved over the years to work better and better with larger sets of data, but in somewhat different ways than something like Fabric, Data Bricks, Snowflake, etc.

Not that there is anything wrong with it, but if it is just different and you need to know what platform you are targeting when trying to apply all the various “do this” advice you will see all over the internet.