The Age-Old SELECT * vs. SELECT 1 Debate

There is a certain look that T-SQL Developers get when they see SELECT * used in production code. In the span of a couple seconds, you’ll see a slight tic develop in their left eye, and you’ll hear little whimpering sounds coming through their nose, which is quickly followed by a not-so-subtle twitching of their lips, almost like they’re holding in bubbling lava before a volcanic explosion. And then there’s a sharp intake of breath and…



It’s not a pretty picture.

But there is a place where a SELECT * is considered okay by some (though not all) developers. And that’s in an EXISTS subquery.

Consider the following query, which finds all Customers in the NorthWind database who do not have any orders:

select CustomerID
from NorthWind.dbo.Customers 
where not exists (select *
                  from NorthWind.dbo.Orders 
                  where CustomerID=Customers.CustomerID)
/*
CustomerID
----------
FISSA
PARIS
*/

Even though a SELECT * is used within the subquery, SQL doesn’t do anything at all with the columns in the SELECT list when it actually runs the query. It only cares about the existence of rows and doesn’t care about the specific attributes of the rows. The only thing that’s really important is the WHERE clause.

In order to firmly demonstrate that the SELECT list is not used, I wrote the following tongue-in-cheek query, which would ordinarily bring about truckloads of runtime errors, but it runs correctly and without any error whatsoever:

select CustomerID
from NorthWind.dbo.Customers 
where not exists (select DivideByZero=1/0
                        ,BadConversion=convert(int,'xxx')
                        ,InvalidParameter=left('abc',-ShipVia)
                        ,DateOverflow=dateadd(year,9999,getdate())
                        ,ArithmeticOverflow=cast(1e308 as tinyint)
                        ,InvalidCursorRef=cursor_status('junk','junk')
                        ,BadSubquery=(select top (-OrderID) EmployeeID 
                                      from NorthWind.dbo.Employees)
                  from NorthWind.dbo.Orders 
                  where CustomerID=Customers.CustomerID)
/*
CustomerID
----------
FISSA
PARIS
*/

The query plan for both of the above queries is exactly identical. It only shows an Index Scan of the Orders.CustomersOrders non-clustered index. And the properties of that Index Scan operator shows that only the CustomerID column is in its output list (because it was specified in the WHERE clause):



Let me show you one other thing I discovered.

Books Online states in the SELECT Clause topic that “the maximum number of expressions that can be specified in the select list is 4096.”

Oh yeah?

This is true of a normal query. Consider the following, where I populate a variable with a comma-separated list of 4097 zeroes, and I incorporate that into a regular SELECT statement. When this is executed, it bombs with the compile-time error message 1056, stating that the SELECT list had over 4096 elements.

declare @columnlist nvarchar(max)
       ,@sql nvarchar(max)
  
--I have to CAST() the first parameter of REPLICATE
--in order to force it to produce more than 8000 bytes
select @columnlist='0'+replicate(cast(',0' as nvarchar(max)),4096)
  
select @sql='select '+@columnlist+' from NorthWind.dbo.Orders'
exec sp_executesql @sql
--Bombs with Msg 1056:  The number of elements in the select
--  list exceeds the maximum allowed number of 4096 elements.

But if I insert that same 4097-element SELECT list into an EXISTS subquery, it runs with no problem whatsoever:

select @sql='select CustomerID
             from NorthWind.dbo.Customers
             where not exists (select '+@columnlist+'
                               from NorthWind.dbo.Orders
                               where CustomerID=Customers.CustomerID)'
exec sp_executesql @sql
/*
CustomerID
----------
FISSA
PARIS
*/

Incredibly, you can give it a SELECT list of 10,000 items and it will compile and execute without error… the compile time takes a while, as it parses the statement and makes sure the syntax is correct, but a compile-time error does not occur, because it just tosses out the SELECT list altogether. And, once again, the query plan is unchanged.

Hey, I can even introduce a GROUP BY and aggregates, and still, they have no impact whatsoever and the same final query plan is generated:

select CustomerID
from NorthWind.dbo.Customers 
where not exists (select ShipVia
                        ,count(*)
                        ,sum(Freight)
                  from NorthWind.dbo.Orders 
                  where CustomerID=Customers.CustomerID
                  group by ShipVia)
/*
CustomerID
----------
FISSA
PARIS
*/

In fact, I can even do an illegal SELECT DISTINCT and it doesn’t even blink an eye. As you can see below, I add a new XML column to the Orders table and then do a SELECT DISTINCT * in the EXISTS subquery. Ordinarily, this would be an illegal operation, because you can’t do a SELECT DISTINCT on non-comparable columns (i.e. XML, TEXT, NTEXT and IMAGE). But heck, it doesn’t matter when it’s in an EXISTS subquery!

alter table NorthWind.dbo.Orders 
add NonComparableColumn xml
go
select CustomerID
from NorthWind.dbo.Customers 
where not exists (select distinct *
                  from NorthWind.dbo.Orders 
                  where CustomerID=Customers.CustomerID)
/*
CustomerID
----------
FISSA
PARIS
*/

And again, the query plan for every single one of all of the above EXISTS queries is exactly the same.

So if the SELECT list doesn’t make any difference in an EXISTS subquery, why do many developers insist on doing a SELECT 1 instead of a SELECT *? Part of it is just a religious purity kind of thing… The * is the Anti-Christ and should be exorcised at all costs. But part of it is also the argument that doing a SELECT * will add unnecessary nanoseconds of compile time to the query because SQL will expand the * into columns and then will turn right around and remove them because they’re unnecessary anyway.

To be absolutely honest, I thought that was nonsense, especially in light of all the illustrative demos I put together above (particularly the SELECT DISTINCT * example). And it just seemed like extra unnecessary work for the compiler to do.

However, I certainly can’t argue with Conor Cunningham, SQL Server Query Optimization Development Lead at Microsoft. He stated back in November 2005 that it may be ever-so-slightly faster to do a SELECT 1. But in a February 2008 blog entry, he stated categorically that the * expansion does happen and that SELECT 1 will avoid the examination of table metadata. You conspiracy theorists may find it interesting that the direct link to this blog entry is no longer functional, but you may be able to see it at this link.

So, I’ll leave it up to you whether to do a SELECT * or a SELECT 1 in your EXISTS subqueries. As for me, I always used to do a SELECT *, but perhaps, to be different, I’ll just do a SELECT 1/0 in all of mine from now on. Sure, it will take a couple of extra femtoseconds to compile (compared to the boring SELECT 1), but the opportunity to do a kind of mischievous “wink wink” will be worth it.