Does `SELECT 1` Offer Performance Gains over `SELECT *` in SQL EXISTS Clauses?

Subquery optimization in EXISTS clause

In SQL programming, the EXISTS operator checks whether a subquery returns any rows. Traditionally, developers used SELECT * syntax in their EXISTS clause. However, some people recommend using SELECT 1 instead, claiming it improves performance.

Answer:

Contrary to the claimed benefits, both Microsoft and the SQL standard state that there is no performance impact from SELECT * in the EXISTS clause. SQL Server will recognize the context and return NO DATA regardless of the columns specified in the select list.

Compare and verify:

To verify this, consider the following code snippet:

<code class="language-sql">IF EXISTS (SELECT 1/0
                 FROM someothertable 
                WHERE a_valid_clause )</code>

If the select list is meaningful, it will cause a divide-by-zero error. However, it executes successfully because EXISTS ignores the actual result set and only checks for the existence of the row.

Conclusion:

In SQL Server, there is no practical difference between using SELECT * or SELECT 1 in the EXISTS clause. Both methods are effective at verifying the presence of rows in a subquery. Developers can choose their preferred syntax based on personal preference or code readability.

The above is the detailed content of Does `SELECT 1` Offer Performance Gains over `SELECT *` in SQL EXISTS Clauses?. For more information, please follow other related articles on the PHP Chinese website!