7

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.

  • 5
    Which DBMS are you using? – a_horse_with_no_name Dec 4 '16 at 18:31
  • 1
    Any answer I'd give to that question would start with, "Clearly, UNION ALL is the most appropriate solution and should be used in any production application. However, if you had to work under this absurd restriction, you could also..." – jpmc26 Dec 5 '16 at 19:17
  • @ypercubeᵀᴹ If you really need 10 copies without joining it to something else, perhaps so. But then why not actually make the question about making 10 copies so that the code duplication becomes an issue? You're obviously in truly extraordinary territory if you need this, too, enough that I would be inclined to step back and ask, "Does my solution even actually make any sense? Is there a better way to do this?" Plus, some DBs coughOraclecough make generating the values on the right of the CROSS JOIN hard without UNION, anyway. – jpmc26 Dec 5 '16 at 23:32
  • Does the output have to be sorted the same way? – Evan Carroll Dec 7 '16 at 1:35
  • 1
    BTW, this is really cool question. – Evan Carroll Dec 7 '16 at 1:42
16
SELECT ta.id, ta.name
FROM emp ta 
CROSS JOIN ( VALUES (1), (2) ) tb (id) ;
| improve this answer | |
  • Which DBs does this work for? – jpmc26 Dec 5 '16 at 23:30
  • I tested with MSSQL – paparazzo Dec 5 '16 at 23:34
  • 2
    @jpmc26 Postgres and DB2 as well. It's standard SQL. – ypercubeᵀᴹ Dec 6 '16 at 11:41
  • 1
    @Paparazzi you should finish your answer with all other simple base types of joins: OUTER JOIN (VALUES (1),(2)) ON true, INNER JOIN (VALUES (1),(2)) ON true and NATURAL JOIN ( VALUES (1),(2) ). They're all the same thing imho. – Evan Carroll Dec 7 '16 at 9:55
  • 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; – Leigh Riffel Jan 10 '17 at 15:10
10

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;
| improve this answer | |
  • Proving that outer joins and unions are 2 aspects of the same coin ;) – ypercubeᵀᴹ Dec 4 '16 at 19:42
  • 1
    Ha. I was thinking LEFT JOIN and going, "there's no way this works." Soon as I saw FULL JOIN it made a lot more sense! – ErikE Dec 5 '16 at 5:27
9

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)) ;
| improve this answer | |
  • UNION is not allowed though – a_horse_with_no_name Dec 4 '16 at 21:49
  • 4
    @a_horse_with_no_name I read the question as "UNION ALL is not allowed" – ypercubeᵀᴹ Dec 4 '16 at 21:50
  • The GROUPING SET one is pretty clever. However UNION isn't allowed and the first suggestion is lame. – Evan Carroll Dec 7 '16 at 1:43
  • @EvanCarroll Can't people read? The question says "UNION ALL" is not allowed. It allows "UNION DISTINCT". About the lame, I won't bother. It's valid SQL. As far as I am concerned, someone might say that the question is lame (and all the answers are lame). Define "lame" first and then we can discuss. – ypercubeᵀᴹ Dec 7 '16 at 8:08
  • @ypercubeᵀᴹ lame in this context means that the leading answer uses a simple CROSS JOIN on VALUES, you're using a NATURAL JOIN on VALUES. Next we going to do an INNER JOIN (VALUES (1),(2)) ON true and then an OUTER JOIN (VALUES (1),(2)) ON true? That methods already been played. – Evan Carroll Dec 7 '16 at 9:54
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.

| improve this answer | |
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.

| improve this answer | |
  • 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 Dec 6 '16 at 7:54
  • 1
    @ypercubeᵀᴹ thanks for reminding me about the cross join, MySQL gave me few bad habits. About the "problem" with row count - I note in the first sentence that you need "any two-row table" and then use the one from the example. But I might have been a bit more specific about the limitations. – jkavalik Dec 6 '16 at 18:48
0

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

| improve this answer | |
  • standards don't get you jobs. cleverness around edge cases wowz all the interviewers. – Evan Carroll Dec 7 '16 at 2:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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