EXISTS Queries: SELECT * or SELECT 1 – Which Subquery Syntax is More Readable?

Subquery Readability in EXISTS Queries

The question of which subquery syntax is more readable in EXISTS queries often arises due to its impact on comprehension and code clarity. While there is no significant difference in performance between the two common approaches, selecting the most suitable option can enhance readability and simplify maintenance.

Initially, databases favored using the syntax "EXISTS (SELECT * FROM baz WHERE baz.id = bar.id)" in their documentation. However, over time, the industry has gravitated towards the simpler form "EXISTS (SELECT 1 FROM baz WHERE baz.id = bar.id)".

The intuitive argument for using "SELECT " is that it signifies a search for the existence of any* record in the subquery. However, this leads to some misconceptions. As stated in industry recommendations, the focus should be on the existence, not the content, of the subquery records.

The syntax "EXISTS (SELECT 1 ..)" ensures that only one record is returned by the subquery. This aligns with the definition of EXISTS as a semi-join, which checks for the presence or absence of records in a specified join condition.

Therefore, it is suggested that the more intuitive and industry-standard approach is "EXISTS (SELECT 1 ..)", as it simplifies the focus on determining existence rather than introducing unnecessary complexity or confusion.

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