SELECT 1 vs. SELECT * in EXISTS Subqueries: Does it Matter for Performance?

*EXISTS subquery optimization: performance difference between SELECT 1 and SELECT **

When using the EXISTS operator in a SQL query, there is a dispute about whether to specify SELECT 1 or SELECT * in the subquery. This article explores the performance impact of both approaches.

**Traditional method: SELECT ***

Traditionally, subqueries use SELECT * because it retrieves all columns in the subquery.

<code class="language-sql">IF EXISTS (SELECT * FROM TABLE WHERE Columns=@Filters)
BEGIN
   UPDATE TABLE SET ColumnsX=ValuesX WHERE Columns=@Filters
END</code>

Alternative method: SELECT 1

Database experts recommend that for EXISTS checks, using SELECT 1 is more efficient since it only returns a single value.

<code class="language-sql">IF EXISTS (SELECT 1 FROM TABLE WHERE Columns=@Filters)
BEGIN
   UPDATE TABLE SET ColumnsX=ValuesX WHERE Columns=@Filters
END</code>

Performance Difference

Despite the purported potential performance benefits of SELECT 1, Microsoft SQL Server is designed to optimize subqueries of the EXISTS operator. Whether you use SELECT 1 or SELECT *, SQL Server does not return data to the system.

Verification

To verify this optimization, try executing the following query:

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

No error will be thrown even if the subquery performs an invalid division. This indicates that the EXISTS list in the SELECT subquery will not be evaluated.

SQL Standard

The ANSI SQL 1992 standard clearly states that when a SELECT list contains only "*" in the EXISTS subquery, it is equivalent to an arbitrary literal.

Conclusion

Based on SQL Server's optimization capabilities and SQL standards, there is no performance difference when using SELECT 1 or SELECT * in an EXISTS subquery. Both methods will return the same results with equal efficiency.

The above is the detailed content of SELECT 1 vs. SELECT * in EXISTS Subqueries: Does it Matter for Performance?. For more information, please follow other related articles on the PHP Chinese website!