CODEWITHSUNDEEP
sqlsql-server
Paul Richards
Paul Richards

Reputation: 1207

When using "IF NOT EXISTS(SELECT..." in Sql Server, does it matter which columns you choose?

Quite a lot of database scripts are of the form:

IF NOT EXISTS(SELECT * FROM Countries WHERE Name = 'France')
INSERT INTO(Countries)

However, I've also seen people do:

IF NOT EXISTS(SELECT CountryID FROM Countries WHERE Name = 'France')
INSERT INTO(Countries)

And even:

IF NOT EXISTS(SELECT 1 FROM Countries WHERE Name = 'France')
INSERT INTO(Countries)

The advantage of the last one is supposedly that its more efficient: the query doesn't actually use any of the columns in the subquery, so it might be quicker to not bring any of them back. But it looks odd, so it strikes me that it might confuse some people. And does it make any difference anyway to the actual execution time?

Upvotes: 7

Views: 8146

Answers (3)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239704

I think it was back in the 6.5 - 7 period of SQL Server that they made the query optimizer smart enough to know that:

IF NOT EXISTS(SELECT * FROM Countries WHERE Name = 'France')

Does not actually need to return any row data. The advice to use SELECT 1 pre-dates that, yet continues on as a myth.

Arguably, it's a fault with the SQL standard - they ought to allow EXISTS to start with the FROM clause and not have a SELECT portion at all.

And from Subqueries with EXISTS:

The select list of a subquery introduced by EXISTS almost always consists of an asterisk (*). There is no reason to list column names because you are just testing whether rows that meet the conditions specified in the subquery exist.

Upvotes: 10

Filipe Silva
Filipe Silva

Reputation: 21657

No. The only thing that matters is if rows are returned or not, That's why SELECT 1 is good enough.

Upvotes: 3

RAS
RAS

Reputation: 3385

No, sql server is smart enough to make this optimization for you.

Upvotes: 3

Related Questions