snippetModerate
How can I display twice each stored row without using UNION ALL or a temporary table?
Viewed 0 times
storedcantwicewithouteachalltemporaryunionusinghow
Problem
The relevant table, named
And the output or result-set of the data manipulation operation should be as shown bellow:
Requirements
The output must be obtained complying with the following conditions:
Note: This scenario was brought up to me by an interviewer.
emp, holds the following data:CREATE TEMPORARY TABLE emp AS
SELECT * FROM ( VALUES (1,'A'), (2,'B'), (3,'C') );
ID Name
-- ----
1 A
2 B
3 CAnd the output or result-set of the data manipulation operation should be as shown bellow:
ID Name
-- ----
1 A
1 A
2 B
2 B
3 C
3 CRequirements
The output must be obtained complying with the following conditions:
- No use of the UNION ALL operator in association with the employed SELECT statement(s)
- No use of temporary table(s)
- No use of an UPDATE operation to the existing table
Note: This scenario was brought up to me by an interviewer.
Solution
Three more ways.
Similar to Paparazzi's answer, making use that a
Another that uses
Abusing
Similar to Paparazzi's answer, making use that a
NATURAL join becomes a CROSS join when there is no common column: SELECT e.id, e.name
FROM emp AS e
NATURAL JOIN
(VALUES (1), (2)) AS c (i) ;Another that uses
UNION DISTINCT and an extra column to avoid the removal of duplicates:SELECT id, name
FROM
( SELECT id, name, 1 AS d
FROM emp
UNION
SELECT id, name, 2
FROM emp
) AS t ;Abusing
GROUPING SETS. There is something unexpected and ironic in this method as it uses GROUP BY to multiply the number of rows returned:SELECT id, name
FROM emp
GROUP BY GROUPING SETS ((id, name), (id, name)) ;Code Snippets
SELECT e.id, e.name
FROM emp AS e
NATURAL JOIN
(VALUES (1), (2)) AS c (i) ;SELECT id, name
FROM
( SELECT id, name, 1 AS d
FROM emp
UNION
SELECT id, name, 2
FROM emp
) AS t ;SELECT id, name
FROM emp
GROUP BY GROUPING SETS ((id, name), (id, name)) ;Context
StackExchange Database Administrators Q#157138, answer score: 10
Revisions (0)
No revisions yet.