EXISTS Subqueries: SELECT * or SELECT 1 – Which is More Readable?

Readability of EXISTS Subqueries

When using EXISTS subqueries, there are two main options:

SELECT foo FROM bar WHERE EXISTS (SELECT * FROM baz WHERE baz.id = bar.id);
SELECT foo FROM bar WHERE EXISTS (SELECT 1 FROM baz WHERE baz.id = bar.id);

From a performance perspective, both are equivalent. However, the question arises about readability.

Manuals and Common Usage

As per the research conducted on various RDBMS manuals and online searches, there seems to be a divide:

• Microsoft SQL, Oracle, and MySQL favor SELECT * in their examples.
• PostgreSQL prefers SELECT 1.
• SQLite does not provide any explicit examples.

The Stack Overflow search results indicate a slightly higher prevalence of SELECT * in code.

Intuitive Approach

The question poses whether SELECT * is more intuitive. However, the answer suggests that it is not the most intuitive choice. Instead, SELECT 1 is recommended because:

• It explicitly states the condition for existence.
• It aligns with the ANSI standard, which specifies that the specific rows returned by the subquery are irrelevant for EXISTS.
• It debunks the myth that SELECT 1 is a shortcut for existence checking.

Conclusion

While there is no clear consensus, SELECT 1 in EXISTS subqueries is argued to be more intuitive. It emphasizes the existence aspect, aligns with standards, and dispels misconceptions. Ultimately, the readability preference may vary depending on the developer's understanding and style.

The above is the detailed content of EXISTS Subqueries: SELECT * or SELECT 1 – Which is More Readable?. For more information, please follow other related articles on the PHP Chinese website!