Introduction

SELECT 1 FROM table returns the constant value 1 for every row that the FROM and WHERE clauses produce. The 1 is not a column name and it does not reference the first column. It is a literal constant expression, and the database evaluates it once per qualifying row. The pattern is most commonly used inside EXISTS subqueries where the only question being asked is whether any matching rows exist at all.

What the Query Actually Returns

Consider a users table with four rows:

SELECT 1 FROM users;

The result set contains four rows, each with a single unnamed column whose value is 1:

1 ?column?
2----------
3        1
4        1
5        1
6        1

The database still performs the full FROM processing. It reads rows, applies any WHERE filters, and honors JOIN conditions. The only difference from a normal SELECT is that the projection is a constant instead of a column reference.

You can use any constant expression. SELECT 42 FROM users, SELECT 'yes' FROM users, and SELECT NULL FROM users all behave the same way. The number 1 is simply the convention.

The Primary Use Case: EXISTS Subqueries

The most important context for SELECT 1 is inside an EXISTS check:

1SELECT d.name
2FROM departments d
3WHERE EXISTS (
4    SELECT 1
5    FROM employees e
6    WHERE e.department_id = d.id
7);

The EXISTS operator does not inspect the values returned by the subquery. It only checks whether the subquery produces at least one row. That is why the projection does not matter. SELECT 1, SELECT *, and SELECT e.id all produce identical results inside EXISTS.

Writing SELECT 1 is a convention that signals intent to the reader: this subquery is purely an existence check, not a data retrieval operation.

EXISTS vs COUNT for Existence Checks

A common alternative to EXISTS is using COUNT(*) > 0. Both answer the same question, but they do different amounts of work.

1-- Counts ALL matching rows, then checks if the count is positive
2SELECT CASE
3    WHEN COUNT(*) > 0 THEN 'yes'
4    ELSE 'no'
5END AS has_employees
6FROM employees
7WHERE department_id = 10;
8
9-- Stops as soon as one matching row is found
10SELECT CASE
11    WHEN EXISTS (SELECT 1 FROM employees WHERE department_id = 10) THEN 'yes'
12    ELSE 'no'
13END AS has_employees;

The EXISTS version can short-circuit. Once the database finds a single qualifying row, it stops scanning. The COUNT(*) version must evaluate every qualifying row to produce the count, even though you only care whether the count is positive.

In most modern query optimizers, the performance difference is negligible for small result sets. For large tables with many matching rows, EXISTS can be significantly faster.

SELECT 1 in Other Contexts

Health Checks and Connectivity Tests

A bare SELECT 1 without a FROM clause is used as a database connectivity test:

SELECT 1;

This query does not touch any table. It simply asks the database to return a constant. Connection pools and health check endpoints use this pattern because it exercises the connection with minimal overhead.

// JDBC connection validation query
dataSource.setValidationQuery("SELECT 1");

Some databases use variations. Oracle requires SELECT 1 FROM DUAL because Oracle does not support SELECT without a FROM clause. MySQL, PostgreSQL, SQL Server, and SQLite all support the bare SELECT 1.

Conditional Logic with CASE

SELECT 1 also appears in CASE expressions and IF checks within stored procedures:

1IF (SELECT 1 FROM sys.tables WHERE name = 'audit_log') IS NOT NULL
2BEGIN
3    -- Table exists, proceed
4    INSERT INTO audit_log (event) VALUES ('startup');
5END

INSERT with Existence Guard

A common pattern uses SELECT 1 in WHERE NOT EXISTS to prevent duplicate inserts:

1INSERT INTO subscriptions (user_id, plan)
2SELECT 42, 'premium'
3WHERE NOT EXISTS (
4    SELECT 1
5    FROM subscriptions
6    WHERE user_id = 42
7);

This inserts the row only if no matching subscription already exists. The SELECT 1 inside the subquery is again an existence check.

What the Optimizer Actually Does

People sometimes believe that SELECT 1 is a performance optimization over SELECT * inside an EXISTS subquery. In practice, modern query optimizers (PostgreSQL, MySQL, SQL Server, Oracle) recognize EXISTS subqueries and optimize them identically regardless of the projection.

You can verify this with an execution plan:

1EXPLAIN ANALYZE
2SELECT d.name
3FROM departments d
4WHERE EXISTS (
5    SELECT 1 FROM employees e WHERE e.department_id = d.id
6);
7
8EXPLAIN ANALYZE
9SELECT d.name
10FROM departments d
11WHERE EXISTS (
12    SELECT * FROM employees e WHERE e.department_id = d.id
13);

Both plans will show the same operations. The optimizer knows that EXISTS only needs row existence, so it does not materialize columns in either case.

The real performance insight is not SELECT 1 vs SELECT *. It is EXISTS vs COUNT(*) > 0, where the optimizer's ability to short-circuit makes a measurable difference on large result sets.

SELECT 1 vs SELECT * vs SELECT column

Inside EXISTS, all three are equivalent. Outside EXISTS, they behave differently because the projection actually matters for the result set.

Common Pitfalls

Thinking 1 refers to the first column in the table is the most common misunderstanding. SQL column indexing in the SELECT list uses names, not positional numbers. The 1 is a literal integer value.

Assuming SELECT 1 has unique performance benefits is misleading. The performance advantage comes from EXISTS short-circuiting, not from the choice of projection.

Forgetting that SELECT 1 FROM table still returns one row per matching source row can produce unexpected result sizes. If the table has a million rows, the query returns a million rows each containing 1.

Using COUNT(*) when the real need is existence checking is a missed optimization. Use EXISTS for yes-or-no questions and COUNT(*) only when you need the actual count.

Treating SELECT 1 as mysterious or special syntax obscures what is a straightforward constant expression. Any valid SQL expression can appear in the SELECT list, and 1 is simply the conventional choice for existence checks.

Summary

• SELECT 1 FROM table returns the literal value 1 for every row the query produces.
• The 1 is a constant expression, not a column reference or positional index.
• The pattern is most common inside EXISTS subqueries, where only row existence matters.
• 'EXISTS short-circuits after finding one row, making it more efficient than COUNT(*) > 0 for existence checks.'
• A bare SELECT 1 (no FROM) is the standard database health check query.
• Modern optimizers treat SELECT 1, SELECT *, and SELECT column identically inside EXISTS.