Add row to query result using select

Question

Is it possible to extend query results with literals like this?

select name from users
union
select name from ('JASON');

or

select age, name from users
union
select age, name from (25,'Betty');

so it returns all the names in the table plus 'JASON', or (25,'Betty').

Solution 1

You use it like this:

SELECT  age, name
FROM    users
UNION
SELECT  25 AS age, 'Betty' AS name

Use UNION ALL to allow duplicates: if there is a 25-years old Betty among your users, the second query will not select her again with mere UNION.

Solution 2

In SQL Server, you would say:

Select name from users
UNION [ALL]
SELECT 'JASON'

In Oracle, you would say

Select name from user
UNION [ALL]
Select 'JASON' from DUAL

Solution 3

is it possible to extend query results with literals like this?

Yes.

Select Name
From Customers
UNION ALL
Select 'Jason'

• Use UNION to add Jason if it isn't already in the result set.
• Use UNION ALL to add Jason whether or not he's already in the result set.

Comments

[Admin]

I'm using sql server 2005 and running a simple union like my first example, but I get "Incorrect Syntax". Is it explicitly like my example?

[Joel Coehoorn]

He specified sql server: "from dual" is oracle only

[Yeonho]

shouldnt the brackets around ALL be omitted for SQL Server?

[Rich.Carpenter]

The brackets around ALL here are indicating it's an optional element.

[ALvin Das]

How would you always return 'Jason' as the first item in the return Union list?

[Alex]

Do I have to union them one by one? Is there a way to add multiple rows at the same time?

[Quassnoi]

@Alex: In SQL Server and PostgreSQL, you can do SELECT age, name FROM users UNION SELECT * FROM (VALUES (25, 'Betty'), (26, 'Fatty')) q(age, name)