Add row to query result using select

Deciding Between union and union all

When debating between UNION or UNION ALL, bear in mind that UNION automatically discards duplicate rows, while UNION ALL retains every row, duplicates as well. Here's a shorthand:

• If distinct results are required and duplicate rows should be eliminated, opt for UNION.
• Where duplicates are meaningful, UNION ALL preserves them. It's also quicker since it doesn't need to sift out unique records.

Combining static and dynamic data

In some cases, you may wish to append rows using data derived from other queries or tables, not just fixed values. The same technique could be utilized:

SELECT column1, column2 FROM your_table
UNION ALL
SELECT some_value, (SELECT MAX(column2) FROM your_table) -- Mixes fixed and dynamic data


Coordinating data types

While using UNION or UNION ALL, make sure the data types of each column are compatible. If one column is integer and another is varchar, casting can be used to align these data types:

SELECT casting_col1 AS varchar, casting_col2 FROM your_table
UNION ALL
SELECT 'StringValue', CAST(12345 AS varchar) -- Don't mix apples and oranges


This ensures the data in the final result set works together flawlessly.

Compatibility with SQL Server and Oracle

In SQL Server, the equivalent of adding a row with hardcoded values would take this form:

SELECT * FROM your_table
UNION ALL
SELECT 'ValueA', 'ValueB', 'ValueC'; -- Directly plugs the values like injecting vitamins


For Oracle, there's a peculiar FROM DUAL statement which serves the same purpose:

SELECT * FROM your_table
UNION ALL
SELECT 'ValueA', 'ValueB', 'ValueC' FROM DUAL; -- Because Oracle likes to be different


In Oracle, DUAL is a special one-row, one-column table that comes preinstalled in all Oracle databases.