EXISTS Clause Optimization: SELECT 1 vs. SELECT * — Does it Matter?

*EXISTS clause performance: SELECT 1 vs. SELECT **

In SQL, the EXISTS clause is used to check whether there are rows matching the specified condition in the subquery. Developers have long used both SELECT 1 and SELECT * in EXISTS queries, which begs the question: Which approach is more efficient?

SQL Server Optimization

According to Microsoft, SQL Server is smart enough to recognize the use of EXISTS regardless of the SELECT list of the subquery. It does not return any data to the system, so the SELECT * and SELECT 1 clauses are equivalent in terms of performance.

According to Microsoft’s documentation:

"The select list of a subquery introduced by EXISTS almost always contains an asterisk (). There is no reason to list the column names, since you are just testing whether there are rows that satisfy the conditions specified in the subquery .*"

Self-Verification

To confirm this behavior you can run the following query:

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

If the SELECT list does participate, this query will trigger a divide-by-zero error. However, because SQL Server understands the context of the EXISTS clause, it does not perform any calculations on the SELECT list.

SQL Standard

The ANSI SQL 1992 standard further clarifies this behavior, stating that in the context of the EXISTS predicate, the select list "*" is equivalent to any literal value.

Conclusion

In SQL Server, selecting SELECT 1 and SELECT * in the EXISTS clause does not affect performance. Both methods produce the same results because the optimizer recognizes their purpose and returns no data to the system.

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