Multiple Methods for Querying Constant Rows in SQL

Introduction

In database development and data analysis, there is often a need to construct virtual tables containing fixed data for testing, data population, or association with other tables. While traditional single-row constant queries are straightforward, they become inadequate when multiple rows of data are required. This article systematically introduces various methods for implementing multi-row constant queries.

Basic Method: UNION ALL Operator

The most universal and cross-database compatible approach is using the UNION ALL operator. This method constructs multiple rows by combining results from several single-row queries.

SELECT 1 AS col1, 2 AS col2, 3 AS col3
UNION ALL
SELECT 4 AS col1, 5 AS col2, 6 AS col3
UNION ALL
SELECT 7 AS col1, 8 AS col2, 9 AS col3

The advantage of this method lies in its broad compatibility, working effectively across almost all mainstream database systems. Each SELECT statement defines one row of data, connected through UNION ALL to form a complete result set. It's important to note that UNION ALL preserves all duplicate rows, while UNION automatically removes duplicates. When constructing constant data, UNION ALL is typically preferred for better performance.

Database-Specific Syntax: VALUES Clause

For PostgreSQL users, the VALUES clause offers a more concise syntax for constructing multiple rows of constant data.

SELECT *
FROM (
VALUES
(1, 2, 3),
(4, 5, 6),
(7, 8, 9)
) AS virtual_table (col1, col2, col3)

This syntax is more intuitive, directly listing multiple rows of data in tuple form, resulting in better readability. When constructing large amounts of test data, this method produces cleaner and more understandable code.

Special Handling in Oracle Database

In Oracle Database, due to syntax requirements, queries typically need to be performed from the DUAL table.

SELECT 1 AS col1, 2 AS col2, 3 AS col3 FROM dual
UNION ALL
SELECT 4 AS col1, 5 AS col2, 6 AS col3 FROM dual
UNION ALL
SELECT 7 AS col1, 8 AS col2, 9 AS col3 FROM dual

DUAL is a special single-row table in Oracle used to satisfy SQL syntax requirements. Although this approach may seem slightly verbose syntactically, it represents standard practice in Oracle environments.

Dynamic Row Generation Techniques

Beyond fixed numbers of constant rows, there are situations requiring generation of specified quantities of rows. Different database systems provide their own solutions.

In Oracle, hierarchical queries can be used:

SELECT level AS row_number
FROM dual
CONNECT BY level <= 10

In SQL Server, recursive CTEs are commonly employed:

WITH number_series (n) AS (
SELECT 1
UNION ALL
SELECT n + 1
FROM number_series
WHERE n < 10
)
SELECT n FROM number_series

PostgreSQL provides built-in functions:

SELECT generate_series(1, 10) AS row_number

Practical Application Scenarios Analysis

Multi-row constant queries find extensive applications in enterprise-level application development. Taking customer service systems as an example, these techniques can be used to construct test data when designing data entry forms with numerous fields.

Considering a customer information table with 35 fields, the UNION ALL method can quickly generate test data:

SELECT
'Company A' AS company,
'John Doe' AS contact,
'Test note' AS notes,
'Broker1' AS broker_name,
-- Other 32 fields...
UNION ALL
SELECT
'Company B' AS company,
'Jane Smith' AS contact,
'Another note' AS notes,
'Broker2' AS broker_name,
-- Other 32 fields...

This approach is particularly suitable for scenarios such as data migration, system testing, and demonstration environment setup.

Performance Comparison and Best Practices

Through performance testing and analysis of different methods, the following conclusions can be drawn:

For small amounts of data (typically less than 100 rows), the UNION ALL method performs well and offers the best compatibility. When dealing with larger datasets, database-specific syntax (such as PostgreSQL's VALUES clause) generally provides better performance.

In practical development, the following recommendations are suggested:

1. Prioritize UNION ALL in cross-database applications
2. Use database-specific optimized syntax in single-database environments
3. Consider specialized test data generation tools for large test datasets
4. Avoid frequent use of large constant queries in production environments

Conclusion

Multi-row constant data querying represents an important technique in SQL development. Mastering different implementation methods can enhance development efficiency and code quality. By appropriately selecting methods suited to specific scenarios, optimal performance can be achieved while ensuring functionality. As database technology evolves, future developments may introduce more optimized syntax and tools to simplify this process.