86

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').

Deduplicator
  • 44,692
  • 7
  • 66
  • 118

3 Answers3

113

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.

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • 1
    Do I have to union them one by one? Is there a way to add multiple rows at the same time? – Alex Jan 21 '21 at 19:03
  • 4
    @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)` – Quassnoi Jan 21 '21 at 20:36
20

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
Mark Sherretta
  • 10,160
  • 4
  • 37
  • 42
18

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.
Amy B
  • 108,202
  • 21
  • 135
  • 185
  • 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? –  Apr 10 '09 at 19:39
  • How would you always return 'Jason' as the first item in the return Union list? – ALvin Das Sep 15 '14 at 17:26