Add row to query result using select

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

166425 次浏览
小开

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.
小开

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.

小开

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

提交答案