8

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.

2
  • 5
    Which DBMS are you using?
    – user1822
    Commented Dec 4, 2016 at 18:31
  • Does the output have to be sorted the same way? Commented Dec 7, 2016 at 1:35

6 Answers 6

18
SELECT ta.id, ta.name
FROM emp ta 
CROSS JOIN ( VALUES (1), (2) ) tb (id) ;
1
  • 1
    This syntax doesn't work for Oracle, but a cross join is the simplest way to do this. For Oracle this would look like this: select id, name from emp cross join (select 1 from dual connect by level <=2) order by 1; Commented Jan 10, 2017 at 15:10
11

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;
1
  • 1
    Proving that outer joins and unions are 2 aspects of the same coin ;) Commented Dec 4, 2016 at 19:42
10

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)) ;
1
  • 4
    I read the question as "UNION ALL is not allowed" Commented Dec 4, 2016 at 21:50
5

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.

1

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.

1
  • 2
    And of course the second query may fail to produce the expected result in the more general case where id might not have 1 or 2.
    – Andriy M
    Commented Dec 6, 2016 at 7:54
1

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

1
  • I'd prefer it if the generate_series() was in the FROM clause. It's rather disturbing that Postgres even allows set generating functions in SELECT and ORDER BY (which can lead to unintuitive/inexplicable results.) To clarify, your example would produce the wanted result. Commented May 12, 2022 at 17:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.