Comprehensive Guide to Selecting from Value Lists in SQL Server

Problem Background and Requirement Analysis

In practical database operations, there is often a need to query and filter data from a fixed set of values. This requirement is particularly common in data processing, temporary data analysis, and testing scenarios. Users initially attempted to use syntax similar to select distinct * from (1, 1, 1, 2, 5, 1, 6), but this is not valid syntax in SQL Server.

Method One: Using UNION SELECT Operations

This is the most straightforward method, especially suitable for handling comma-separated values from text files or external data sources. The core concept involves treating each value as an independent SELECT statement and then combining the results through UNION operators.

SELECT 1
UNION SELECT 1
UNION SELECT 1
UNION SELECT 2
UNION SELECT 5
UNION SELECT 1
UNION SELECT 6

In practical applications, if data exists as long comma-separated text, the query statement can be quickly generated using text editor find-and-replace functionality:

1. Replace each comma in the original text with UNION SELECT
2. Add the SELECT keyword at the beginning of the statement
3. The UNION operator automatically removes duplicate values, achieving DISTINCT effect

The advantages of this method include simple and understandable syntax, good compatibility, and applicability to all versions of SQL Server. The disadvantage is that when dealing with large numbers of values, the query statement becomes lengthy and may affect readability.

Method Two: Using Table Value Constructors

SQL Server 2008 and later versions provide a more elegant solution—table value constructors. This method uses the VALUES clause to create temporary tables for query operations.

SELECT DISTINCT Field1 
FROM (
    VALUES
        (1), 
        (1), 
        (1), 
        (2), 
        (5), 
        (1), 
        (6)
) AS TempTableName (Field1)

The syntax structure of table value constructors is clearer:

SELECT DISTINCT FieldName1, FieldName2, ..., FieldNameN
FROM (
    VALUES
        (ValueForField1, ValueForField2, ..., ValueForFieldN),
        (ValueForField1, ValueForField2, ..., ValueForFieldN),
        ...
) AS TempTableName (FieldName1, FieldName2, ..., FieldNameN)

The main advantages of this method include:

• More concise and clear syntax
• Support for constructing multi-column data
• Greater flexibility when used as derived tables in FROM clauses
• Generally better performance compared to multiple UNION SELECT combinations

It's important to note that when used as the VALUES clause in INSERT statements, table value constructors support a maximum of 1000 rows. If this limit is exceeded, alternative methods or treatment as derived tables is required.

Method Three: Using the IN Operator

Although not directly mentioned in the Q&A data, the IN operator is another important method for handling value list queries, particularly suitable for WHERE condition filtering.

SELECT * FROM YourTable
WHERE YourColumn IN (1, 1, 1, 2, 5, 1, 6)

The essence of the IN operator is a shorthand for multiple OR conditions. The above query is equivalent to:

SELECT * FROM YourTable
WHERE YourColumn = 1 OR YourColumn = 1 OR YourColumn = 1 
    OR YourColumn = 2 OR YourColumn = 5 OR YourColumn = 1 OR YourColumn = 6

The IN operator can also be combined with subqueries to achieve more complex data filtering:

SELECT * FROM Customers
WHERE CustomerID IN (SELECT CustomerID FROM Orders)

Data Type Handling and Conversion

When using table value constructors, attention must be paid to implicit data type conversion rules. SQL Server performs data type conversion according to UNION ALL syntax characteristics, converting mismatched types to data types with higher precedence.

CREATE TABLE dbo.t (a INT, b CHAR);
INSERT INTO dbo.t VALUES (1,'a'), (2, 1);

In this example, SQL Server will attempt to convert the character 'a' to an integer because integer data types have higher precedence than character types. If the conversion fails, an error will occur. To avoid this situation, explicit type conversion should be performed:

INSERT INTO dbo.t VALUES (1,'a'), (2, CONVERT(CHAR,1));

Practical Application Scenario Analysis

Based on the user's description of data sources—unformatted text files from clients—the recommended processing workflow is as follows:

1. Use text editors or script tools to convert comma-separated text into valid SQL queries
2. For temporary queries, the UNION SELECT method is most convenient
3. For value lists that need to be reused, table value constructors are recommended
4. If value lists need to be associated with other tables, consider creating temporary tables or table variables

Performance Comparison and Best Practices

The three methods have distinct performance characteristics:

• UNION SELECT: Suitable for small data volumes, simple syntax but potentially complex execution plans
• Table Value Constructors: Better performance, more optimized execution plans, suitable for medium data volumes
• IN Operator: Best performance when used in WHERE conditions, but requires existing table structures

Best practice recommendations:

• For temporary, one-time queries, prioritize UNION SELECT
• In SQL Server 2008 and later versions, recommend using table value constructors
• When the number of values exceeds 1000, consider using derived tables or other batch processing methods
• In production environments, recommend creating dedicated reference tables for frequently used value lists

Conclusion

SQL Server provides multiple methods for selecting data from value lists, each with its applicable scenarios and advantages. The UNION SELECT method, with its simplicity and compatibility, is the best choice for handling temporary data; table value constructors excel in syntax conciseness and performance; and the IN operator plays an important role in conditional filtering. Understanding the principles and applicable scenarios of these methods helps developers choose the most appropriate solution based on specific requirements, improving data processing efficiency and code quality.