# Why is it faster to do four simple selects instead of one select with left joins?

I need to find a single value from a table `A` containing three foreign keys to two other tables `B` and `C`.

For the sake of experiment, I tested two ways to query the value:

Multiple queries:

``````declare @start int = (select top 1 [Id] from [B] where [Day] = '2015-01-01')
declare @end int = (select top 1 [Id] from [B] where [Day] = '2017-06-14')
declare @category int = (select top 1 [Id] from [C] where [Title] = 'Hello, World!')

select top 1 [Name]
from [A]
where [StartId] = @start
and [EndId] = @end
and [CategoryId] = @category
and [Day] = '2016-05-27'
``````

Single query:

``````select top 1 [Name]
from [A]
left join [B] as [BStart] on [BStart].[Id] = [A].[StartId]
left join [B] as [BEnd] on [BEnd].[Id] = [A].[EndId]
left join [C] on [C].[Id] = [A].[CategoryId]
where [BStart].[Day] = '2015-01-01'
and [BEnd].[Day] = '2017-06-14'
and [C].[Title] = 'Hello, World!'
and [A].[Day] = '2016-05-27'
``````

I was surprised that the execution plan indicates that the single query is more expensive than multiple queries. When doing all five `select`s together, the one with `left join`s indicates 53%. The other four queries indicate 12% each.

Those are the execution plans:

``````declare @start int = (select top 1 [Id] from [B] where [Day] = '2015-01-01')`
`````` ``````declare @end int = (select top 1 [Id] from [B] where [Day] = '2017-06-14')
``````

(Same as below)

``````declare @category int = (select top 1 [Id] from [C] where [Title] = 'Hello, World!')
`````` ``````select top 1 [Name]
from [A]
where [StartId] = @start
and [EndId] = @end
and [CategoryId] = @category
and [Day] = '2016-05-27'
`````` ``````select top 1 [Name]
from [A]
left join [B] as [BStart] on [BStart].[Id] = [A].[StartId]
left join [B] as [BEnd] on [BEnd].[Id] = [A].[EndId]
left join [C] on [C].[Id] = [A].[CategoryId]
where [BStart].[Day] = '2015-01-01'
and [BEnd].[Day] = '2017-06-14'
and [C].[Title] = 'Hello, World!'
and [A].[Day] = '2016-05-27'
`````` Why is the single query with `left join`s slower than the first approach?

• A `top(n)` without an `order by` returns the first N number of rows ramdom. How big are the tables ? May 30 '16 at 15:51
• Your where clause converts all your "left" joins to inner joins BTW. May 30 '16 at 17:52
• I haven't mentioned that `[B].[Day]` and `[C].[Title]` have an unique index, which means that the lack of `order by` doesn't make the result non-deterministic. May 31 '16 at 10:09
• As an aside: I've intermittently hit scenarios where a union ran more slowly than individual selects. After a bit of experimentation, I determined that sometimes the query planner decides not to parallelize a union, causing CPU-bound queries to take far more time (though slightly less total CPU time). And CPU-bound queries are not so uncommon if your query is running entirely in RAM. Not what happened to you, but worth noting because SQL can be very finicky about deciding whether to parallelize, even with queries that are semantically very similar. May 31 '16 at 13:22