HiveBrain v1.2.0
Get Started
← Back to all entries
snippetModerate

How can I display twice each stored row without using UNION ALL or a temporary table?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
storedcantwicewithouteachalltemporaryunionusinghow

Problem

The relevant table, named 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    C


And 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    C


Requirements

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