Introduction

Sometimes you need to return a small fixed set of rows without reading a full table scan. This happens in quick reports, feature flags, demo datasets, or temporary migration scripts. SQL provides multiple clean ways to express hardcoded row selection, and the best choice depends on readability and whether you need to join that data with real tables.

Using IN for Existing Table Rows

If the rows already exist in a table and you know the keys, IN is usually the shortest and clearest option.

SELECT id, name, status
FROM users
WHERE id IN (101, 205, 333, 404);

This is easy to read and works well for short lists. For larger lists, maintainability drops and query plans can become less predictable depending on the database engine.

For string keys:

SELECT code, description
FROM product_categories
WHERE code IN ('BOOKS', 'GAMES', 'HOME');

Keep list size reasonable and consider a temporary table if the list grows.

Building Hardcoded Rows With VALUES

When the rows are synthetic and not stored yet, VALUES is a strong pattern. It creates an inline table that can be selected directly or joined.

1SELECT v.id, v.label, v.priority
2FROM (
3    VALUES
4        (1, 'alpha', 10),
5        (2, 'beta', 20),
6        (3, 'gamma', 30)
7) AS v(id, label, priority);

This pattern keeps columns aligned and avoids repeated SELECT statements. It is especially useful in migration scripts where you seed a small lookup set.

You can also join it to real data:

1WITH wanted AS (
2    SELECT *
3    FROM (
4        VALUES
5            (101, 'premium'),
6            (205, 'basic')
7    ) AS t(user_id, expected_plan)
8)
9SELECT u.id, u.plan, w.expected_plan
10FROM users u
11JOIN wanted w ON u.id = w.user_id;

UNION ALL for Cross Database Compatibility

Some engines have syntax differences around VALUES placement. UNION ALL is widely supported and explicit.

1SELECT 1 AS id, 'alpha' AS label
2UNION ALL
3SELECT 2 AS id, 'beta' AS label
4UNION ALL
5SELECT 3 AS id, 'gamma' AS label;

This is verbose for larger sets but works almost everywhere. Use it when you need portable SQL across multiple vendors.

Choosing the Right Pattern

Use IN when filtering existing table rows by key. Use VALUES when generating inline records for joins or expected result sets. Use UNION ALL when dialect constraints make VALUES awkward.

In production code, prioritize clarity over micro optimization for small hardcoded sets. Keep the intent obvious so later maintainers know whether values are test data, business constants, or migration artifacts.

Parameterized Alternatives in Application Code

If hardcoded values come from user choices, do not build SQL text by string concatenation. Use parameterized queries and pass values through the database driver. For engines that support table valued parameters or temporary tables, this pattern remains safe and scales better than expanding long literal lists.

In migration tooling, keep inline rows near the migration step that uses them and add comments describing business meaning. That way reviewers can distinguish temporary patch data from long term reference data.

Common Pitfalls

• Hardcoding long lists in application strings: this harms readability and can hit query length limits.
• Mixing data types in inline rows: implicit casts can degrade performance or fail unexpectedly.
• Forgetting aliases for VALUES columns: many engines require explicit column names.
• Using UNION instead of UNION ALL: duplicate elimination adds unnecessary work.
• Keeping temporary hardcoded rows in permanent code paths: stale values become hidden bugs.

Summary

• Use IN for short key filters on existing tables.
• Use inline VALUES when you need a small derived table.
• Use UNION ALL for broad SQL dialect compatibility.
• Name inline columns clearly to avoid type and readability issues.
• Promote large or long lived hardcoded sets into real tables.