EXISTS Subqueries: SELECT 1 or SELECT * – Does it Matter for Performance?

*EXISTS subquery: SELECT 1 vs. SELECT **

When using EXISTS subquery in SQL, a common question is: Should SELECT 1 or SELECT * be used in the subquery?

*SELECT vs. SELECT 1**

In the past, it was thought that SELECT 1 was more efficient than SELECT * in the EXISTS subquery. However, this is not the case. SQL Server optimizes the EXISTS check to return NO DATA to the system regardless of the columns specified in the subquery's SELECT list.

Microsoft’s perspective

According to Microsoft: "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 ”

Demo

To verify this, consider the following query:

SELECT whatever
FROM yourtable
WHERE EXISTS( SELECT 1/0
FROM someothertable 
WHERE a_valid_clause )

If the SELECT * actually performs any operation on the selected columns, the query will result in a divide-by-zero error. However, it doesn't, indicating that the subquery's SELECT list is ignored and only used to determine the existence of the row.

SQL Standard

The

SQL standard also supports this view: "If '' is contained only in a that is directly contained in an , then '' is equivalent to a A for any ”

.

Conclusion

In summary, there is no performance difference using SELECT 1 or SELECT * in the EXISTS subquery. Therefore, it is recommended which option is more readable or consistent with your coding style.

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