Why 1 Beats * in Certain SQL Queries (And When to Use it)

“1” might seem like just a simple number, but as we know in any programming language or the computing world, 1 plays a significant role. So, what’s its role in SQL, and why do so many developers use 1 in places like EXISTS or dynamic queries?

Whether it’s in subqueries, dynamic scripts, or as a placeholder, understanding how and why to use 1 can make your queries simpler and more efficient. Let’s explore how and when to use “1” in your SQL queries.

The Basics: What Is 1 in SQL

In SQL, 1 is a literal value, meaning it’s just a constant — it doesn’t reference any table columns. Think of it as a placeholder that fits perfectly into SQL’s syntax without causing any extra work. By using 1, you’re not pulling unnecessary data from tables; you’re simply satisfying the query’s structure, keeping things efficient and clean. Let’s go through a few examples, starting with the most simple one where we will select a constant value without using a table.

SELECT 1 AS constant_value;

In this query, we’re selecting the constant value1 and labelling it as constant_value. No tables are involved, and no data is being fetched — it’s just the number 1, making it a lightweight and efficient way to generate a result. It may seem simple, but it has practical uses in SQL, especially for debugging, placeholders, and test queries. In the next example, we will still select a constant table but this time including the table.

SELECT 1 AS flag, employee_name  
FROM employees;

Here, "flag" is always set to 1 for every row, which makes it very usefull in cases where you need a placeholder column for testing or combining results with other queries. This approach is also helpful when you need a fixed value in reports or structured queries, ensuring consistency across results.

In the next example we will use the same structure but this time we will add a condition. Let’s filter the results from the example above using WHEREclause:

SELECT 1 AS flag, employee_name  
FROM employees
WHERE department_id=5;

This query returns only employees from department 5, but each row still includes 1as a placeholder. When troubleshooting a complex query, sometimes you want to check if a certain condition or join is working correctly without pulling unnecessary data. Instead of selecting actual columns, you can use this: If this returns rows, you know the condition is working without loading full employee data.

Where 1Shines in SQL?

Ok, now to the important part — where does 1 shine in SQL? Here are three key scenarios:

1. Using 1 in EXISTS Subqueries — When using EXISTS, developers prefer SELECT 1over SELECT *for both clarity and performance. The EXISTS clause doesn’t actually retrieve any data; it just checks whether a matching row exists. Using SELECT 1 makes this intent clear — it tells the database we only care about existence, not specific columns. For example:

SELECT * 
FROM employees 
WHERE EXISTS (
    SELECT 1 
    FROM departments 
    WHERE departments.id = employees.department_id
);

2. Using 1 for Creating Test Queries — Before running a final query, you can use SELECT 1 to quickly check if the structure works like in the example below:

SELECT 1
FROM employees 
WHERE EXISTS (
    SELECT 1 
    FROM departments 
    WHERE departments.id = employees.department_id
);

This use case involves using SELECT 1 for testing purposes, typically during query development. If this returns results, you know your EXISTScondition is valid before running a more complex query. This can be useful for debugging, ensuring that the logic of the subquery is correct, without running the entire query with complex or large datasets.

Even if the second example looks similar to the first one, in the first use case we are focusing on improving query efficiency and clarity within EXISTS (you don’t need the actual data from the subquery). The second use case is about quickly testing or debugging the query structure before finalizing it. Here, SELECT 1 serves as a placeholder to ensure the query logic is correct without fetching unnecessary data.

3. Using 1=1 in Dynamic SQL for Easy Query Building and Always-True Conditions — One of the most common places you’ll see 1=1 is in dynamic SQL, where queries are built on the fly. The trick here is that 1=1 is always true, making it a handy way to structure flexible WHEREclauses. For example:

SELECT * 
FROM employees 
WHERE 1=1 
  AND department_id = 5;

At first glance, 1=1might seem unnecessary, but it makes adding conditions dynamically much easier. Imagine you’re building a query with multiple optional filters, depending on user input. If you don’t know in advance which conditions will be included, you can start with1=1 and safely append additional filters without worrying about syntax issues or special logic to handle whether it’s the first condition or not. An example is a dynamic SQL below:

DECLARE @sql NVARCHAR(MAX);  
SET @sql = 'SELECT * FROM employees WHERE 1=1';  

IF @IncludeDepartment = 1  
    SET @sql = @sql + ' AND department_id = 5';  

IF @IncludeRole = 1  
    SET @sql = @sql + ' AND role = ''Manager''';  

EXEC sp_executesql @sql;

Common Misconceptions About 1

Myth 1: “ 1 improves query performance ”

Some believe that replacing SELECT * with SELECT 1 will make queries run faster. While this is true in some cases — like in EXISTS subqueries — it’s not a general rule. SELECT 1 doesn’t inherently speed up a query unless it avoids unnecessary data retrieval. In an EXISTS clause, SELECT 1 is preferred because we only check for the presence of rows, not fetch actual data

*Myth 2: “ You can always use 1 instead of ”**

Another misconception is that SELECT 1 can replace SELECT *in all cases. While 1 works in scenarios like EXISTSor test queries, it cannot be used when actual data is needed. If you need to fetch a column from a table, you must use SELECT * or specify the exact columns.

Conclusion

The key is knowing when to use it — and when not to. The best practice is to use it within subqueries with EXISTSas 1 is ideal when checking for row existence because it makes the intent clear and avoids unnecessary data retrieval. It also simplifies dynamically built queries by allowing additional ANDconditions without special handling.

Practice adding 1 in your subqueries, experiment with dynamic conditions using 1=1, and see how it fits into your workflow. The more you use it, the more natural it’ll feel!