-1

I am trying to do the following:

select * from main_genre
join (select 'SDBUY' UNION 'HDBUY') x

The results I'm trying to get are:

'Action', 'SDBUY'
'Action', 'HDBUY'
'Comedy', 'SDBUY',
'Comedy', 'HDBUY'

What would be the proper query from this in mysql?

David542
  • 104,438
  • 178
  • 489
  • 842
  • 1
    Either `CROSS JOIN` or comma `,` instead of `JOIN`. – dnoeth Mar 12 '18 at 22:47
  • Also need to do `UNION SELECT...` and not just `UNION`. – David542 Mar 12 '18 at 22:49
  • @dnoeth what type of join does it do by default if you don't specify it in mysql? – David542 Mar 12 '18 at 22:51
  • In Standard SQL the keywords `inner`& `outer` are optional, `join` is an inner join, `left` & `right` are outer joins. Comma-delimited is older syntax, before they introduced the join keyword, where the join-condition is written as part of the WHERE and when it's ommited you get a `cross join`. – dnoeth Mar 13 '18 at 07:22

2 Answers2

1

Do you want this?

select g.genre, x.what
from (select 'Action' as genre union all select 'Comedy') g cross join
     (select 'SDBUY' as what union all select 'HDBUY') x;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks for this. Why do you do a `union all` here instead of a normal `union` ? – David542 Mar 12 '18 at 22:48
  • 2
    @David542 . . . A `union` is *not* normal. It incurs the overhead of removing duplicates. You should *always* use `union all`, unless you specifically want to incur that overhead. – Gordon Linoff Mar 13 '18 at 01:44
0

Use a cross join and make sure each unioned statement has a select in it:

select * from main_genre
cross join (
  select 'SDBUY'
   union all
  select 'HDBUY'
) x

If the join type isn't specified in MySQL, it will use an inner join: What is the default MySQL JOIN behaviour, INNER or OUTER?.

David542
  • 104,438
  • 178
  • 489
  • 842