sql - mysql query combine table from one table -

my mysql table looks this:

word1  word2  count      c      1      d      2      e      3      f      4 b      c      5 b      d      6 b      g      7 b      h      8 

"a" , "b" user inputs - select * table word1='a' or word1='b' - gets ~10000 rows

i need query get: word1 column intput "a"

word1_ column input "b"

word2 , word2_ same column 1 of them can ignored

i need combine table below table above. example query:

select    t1.word1, t1.word2, t1.count,    t2.word1 word1_, t2.word2 word2_, t2.count count_ table t1 join table t2 on t1.word2 = t2.word2 t1.word1 = 'a' , t2.word1 = 'b' 

produces

word1   word2   count   word1_  word2_  count_         c       1       b       c       5       d       2       b       d       6 

i need count=0 word2 not found.

word1  word2  count  word1_  word2_  count_      c      1      b       c       5      d      2      b       d       6      e      3      b       e       0      f      4      b       f       0      g      0      b       g       7      h      0      b       h       8 

p.s. table has 11million rows index set on word1

p.p.s. provided answer work took 20 sec complete query. need programmatically myself better performance.

you need full outer join... doesn't exist in mysql.

you can way.

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

see sqlfiddle