How should I write such a sql-CodePudding

I have a table like this.

id | a | b
1  | xxx | yyy |
2  | aaa | bbb |

Now, I have a value like yes or no. I want to full join them like following. For each record in table. I want to double it into two records, each one with yes or no.

How should I write such a sql? Thx. I am using MySQL.

id | a | b | whatever
1  | xxx | yyy | yes
2  | xxx | yyy | no
3  | aaa | bbb | yes
4  | aaa | bbb | no

CodePudding user response：

You can simply cross join using a derived table:

select Row_Number() over(order by t.id, w.Whatever desc) Id,
    t.a, t.b,
    w.Whatever 
from (select 'Yes' Whatever union all select 'No') w
cross join t

CodePudding user response：

If your original id is sequential, this could also work

SELECT t.id * 2 - n id, t.a, t.b, w.whatever
FROM t
CROSS JOIN (
  SELECT 'no' whatever, 0 n
  UNION ALL
  SELECT 'yes', 1
) w
ORDER BY id

Or using a variable to generate row numbers

SELECT @id := @id   1 id, t.a, t.b, w.whatever
FROM t
CROSS JOIN (
  SELECT 'no' whatever
  UNION ALL
  SELECT 'yes'
) w
CROSS JOIN ( SELECT @id := 0 ) i
ORDER BY id