A few weeks ago, I wrote this post “This post is not only about SELECT *”. It was primarily about the various reasons to avoid using SELECT * in your reusable code, but it was also about being careful when you teach people things to lean a lot harder on principles that are always true.

For example, it is often said that SELECT * makes your queries slower. In a nuanced way, this is often true, but only if changes occur in the database where columns are added. So many readers (myself included) see something that is demonstrably not 100% being treated as such, and they tune out.

There are plenty of other reasons you shouldn’t use that construct, no matter what.

In this post, I want to admit to having my mind changed, and I will go back and change the previous post.

That was not the concern; this is

In the blog, I stated that I preferred using SELECT * in an EXISTS expression. But I am here to retract that.

While the cost of using SELECT * versus SELECT <Literal>, or any syntactically correct set of columns/expressions/aggregates: typically SELECT 1 is minuscule, it exists. And as my sub-point on the previous blog states, when something is always true, it is worth considering.

The evidence

Soon after I posted this, Kevin Feasel included the post on his CuratedSQL site and added:

As far as SELECT * or SELECT 1 in EXISTS clauses goes, I used to be in Louis’s camp, though I had a former manager who explained her preference for the latter: if you standardize on SELECT 1 in those clauses, it makes it a lot easier to find inappropriate uses of SELECT * in application code.

I agree here, it is easier to do things like search for '%SELECT_*%' (or the better version using RegEx that doesn’t care how much whitespace is between SELECT and *. Then you can find cases of SELECT * and not need to ignore those in EXISTS expressions. A great point.

Then I got a comment from someone who said:

Stopped reading at “Well, okay, I do prefer SELECT * over SELECT 1 in a subquery”

This was not exactly the most convincing argument I have come across, but it was interesting just how this statement could cause such a curt reply.

And then Brad Schulz, a name I have known for years as we were Microsoft MVPs at the same time, commented:

You may want to read this, which I wrote (YIKES!) 17 years ago:

SELECT Blog FROM Brad.Schulz CROSS APPLY SQL.Server(): The Age-Old SELECT * vs. SELECT 1 Debate

In his post, Brad writes a blog post in a style that I love so much. He tested the heck out of what you might find in a SELECT clause, especially in an EXISTS expression, and it was so very thorough that I have no needs/desire to actually try anything myself, and it all seems relevant to this day. Go read it, it is fun!

In the blog, he seems to make my point for me. Pretty much no matter what you put into the SELECT clause, EXISTS seems to ignore it (or at least it is not evaluated during execution). For a simple example:

SELECT 'Not Star'
FROM   Sales.SalesOrderHeader
WHERE  EXISTS (SELECT 1/0, 0/1, 1/0/1/0
               FROM   Sales.SalesOrderHeader);

While this code is nonsensical, you can see in the SELECT clause in the EXISTS expression, I have at least included a divide by 0, and a few other expressions. Any legal expression will work here, and Brad knocks that out of the park in his blog.

The knockout punch

In Brad’s blog, he includes a reference that I can’t ignore. This is from one of my heroes of relational databases (I have quite a few, but Conor Cunningham is absolutely one of them). In this blog:

EXISTS Subqueries: SELECT 1 vs. SELECT – Conor Cunningham

He says:

As such, I typically use SELECT 1. In my blog post I didn’t.

He explains why better than I can, so you ought to give it a read. I won’t argue with Conor (any more!). I also love the humor in that post title and quote.

Summary

While no one is going to argue that EXISTS(SELECT * is a major performance killer, it can be argued that EXISTS(SELECT 1 is demonstrably better in SQL Server for multiple reasons at the very least. Case closed as far as I can see.