mysql query combine table from one table

database, mysql, optimization, sql

Solution

You need a FULL OUTER JOIN... which doesn't exist in mysql.

You can do it this way.

select 
      t1.word1, t1.word2, t1.count, 
      coalesce(t2.word1, 'b') as word1_, t1.word2 as word2_, coalesce(t2.count, 0) as count_
from table1 t1
left join table1 t2 on t1.word2 = t2.word2 and t2.word1 = 'b'
where t1.word1 = 'a' 
union
select 
      coalesce(t2.word1, 'a'), t1.word2 , coalesce(t2.count, 0),
      t1.word1 as word1_, t1.word2 as word2_, t1.count

from table1 t1
left join table1 t2 on t1.word2 = t2.word2 and t2.word1='a'
where t1.word1 = 'b'

see SqlFiddle

Problem

My mysql table looks like this: ``` word1 word2 count a c 1 a d 2 a e 3 a f 4 b c 5 b d 6 b g 7 b h 8 ``` "a" and "b" are user inputs - select * from table where word1='a' or word1='b' - gets ~10000 rows I need a query to get: word1 is column for intput "a" word1_ is column for input "b" word2 and word2_ are the same column so one of them can be ignored i need to combine table below from table above. for example this query: ``` select t1.word1, t1.word2, t1.count, t2.word1 as word1_, t2.word2 as word2_, t2.count as count_ from table t1 join table t2 on t1.word2 = t2.word2 where t1.word1 = 'a' and t2.word1 = 'b' ``` produces ``` word1 word2 count word1_ word2_ count_ a c 1 b c 5 a d 2 b d 6 ``` I need to get count=0 where word2 is not found. ``` word1 word2 count word1_ word2_ count_ a c 1 b c 5 a d 2 b d 6 a e 3 b e 0 a f 4 b f 0 a g 0 b g 7 a h 0 b h 8 ``` P.S. the table has 11million rows index is set on `word1` P.P.S. Provided answer does work but it took 20 sec to complete the query. I will need to do this programmatically by myself to get better performance.

Original source