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

Question

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.

Answer 1

SELECT ta.id, ta.name
FROM emp ta 
CROSS JOIN ( VALUES (1), (2) ) tb (id) ;

Answer 2

One way would be

SELECT COALESCE(a.id, b.id) AS id,
       COALESCE(a.name, b.name) AS name
FROM emp a 
     FULL OUTER JOIN emp b ON 1=0
ORDER BY id;

Answer 3

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)) ;

Answer 4

Another solution that only works with the sample data given.

select e1.*
from emp e1
  join emp e2 on e1.id <> i2.id;

If the requirement or the sample data was only a little bit different this wouldn't work. But the requirement to double the number of rows fits with the sample data that contains exactly two IDs that are different for each ID. If there were 4 different IDs this would not wokr.

Answer 5

The only thing you really need is a cross join to any two-row table. You can use the one you already have in there.

select e1.*
from emp e1
  cross join (select 1 from emp limit 2) tmp;

http://sqlfiddle.com/#!9/15057/3 - uses an implicit temporary table, so might be forbidden too.

select e1.*
from emp e1
  join emp e2 on e2.id IN (1, 2)
order by e1.id;

http://sqlfiddle.com/#!9/15057/6 - you just pick any two rows using the IN() condition and use them to generate the duplicates.

Answer 6

If you're using PostgreSQL, put a SET RETURN FUNCTION in the ORDER BY clause.

CREATE TABLE foo AS
SELECT *
FROM ( VALUES (1,'A'),(2,'B'),(3,'C') ) AS x(id,name);

And, then

SELECT id,name
FROM foo
ORDER BY 1, generate_series(1,2);

 id | name 
----+------
  1 | A
  1 | A
  2 | B
  2 | B
  3 | C
  3 | C

Doesn't have to be generate_series you can also unnest('{1,2}'::int[]), and you can do it the select list too (except you'll get the series in the output).