Subquery using Exists 1 or Exists *

sql

exists-clause

sql-server

performance-optimization

byAlex Kataev·Oct 24, 2024

-- Fasten your seatbelts! We're going for a SQL ride!
SELECT * FROM parent WHERE EXISTS 
-- Any record for this parent? Using "SELECT 1"... just for suspense 
    (SELECT 1 FROM child WHERE parent.id = child.parent_id);

Both EXISTS (SELECT 1 ...) and EXISTS (SELECT * ...) are used to verify the existence of related records, without fetching data. Interchangeable in terms of performance, both are optimized by SQL. The snippet above efficiently checks the presence of child records for a parent.

Unlocking SQL Server's 'EXISTS'

The functionality of the EXISTS clause is a truth-check for the presence of rows within a subquery. The specific data within these rows is irrelevant - EXISTS deals in binary, a simple 'true' or 'false'.

Compilation vs Execution

To appreciate the answer, we need to distinguish between query compilation and execution. While 'SELECT 1' might save a fraction of time during compilation (it doesn't check columns), it bears no impact on actual execution time, resulting in identical execution plans.

Handling Permissions

Intriguingly, both "SELECT 1" and "SELECT *" invoke permission checks on underlying table columns. However, using "SELECT some_not_null_col" results in SQL Server checking permissions for only that column. This can be valuable in terms of access control, but it rarely impacts performance.

Considerations: 'SELECT 1' vs. 'SELECT *'

Know Your Environment

To distinguish between the two, test both in your environment and keep a close eye on the performance metrics. Scripts can measure transactions per second, creating an insightful comparison.

Expert Advice

Advice from database gurus or DBAs on using 'SELECT 1' seldom affects your performance dramatically. However, understanding their reasoning, and validating it based on your experiences, is a nugget of wisdom worth considering.

Code Semantics

Selecting between the two also depends on your team's coding conventions. In complex queries, 'SELECT 1' can make the intention clear, improving overall code readability and maintainability.

SQL Server's Optimization Similarities

Standardized SQL Treatment

SQL Server optimizer, the unsung hero, knows the EXISTS clause doesn't care if it's checking for 1 or *. It's bothered about whether any row exists, as per the SQL Standard, relying uniformly across SQL platforms.

The Execution Plan Insight

Eager to debunk the '* vs 1' myth? Understand the execution plans. Irrespective of what you're selecting, the plans mirror each other. Use them as a compass for your optimization journey.

explain-codes / Sql / Subquery using Exists 1 or Exists *

Linked

How do SQL EXISTS statements work?



Sql NOT IN not working



How to use SQL Select statement with IF EXISTS subquery?



How to select all records from one table that do not exist in another table?



Sql query to find record with ID not in another table

