MySQL: combining the results of two columns in one expression

Question

Let's just say that I have 2 result sets as follows

R1 R2 | 1 | | 5 | | 2 | | 6 | | 3 | | 7 | | 4 | | 8 | 

I need to combine this result into one SET, so I would have something like this:

  R3 | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | 

Currently, I am doing this with UNION as follows:

 SELECT c_1 AS result FROM table WHERE c_1=3 OR c_2=3 UNION SELECT c_2 AS result FROM table WHERE c_1=3 OR c_2=3 

Basically, I end up doing the same operation twice in a table, Just fetch different rows every time. Is there a more efficient way I could do this? I need the result in a single column, since this is an IN constraint. Ultimately, I need to do this.

 SELECT name FROM person WHERE person_id IN (SELECT c_1 AS result FROM table WHERE c_1=3 OR c_2=3 UNION SELECT c_2 AS result FROM table WHERE c_1=3 OR c_2=3) 

Is there a better way to find all this? Any help is appreciated.

Answer 1

Instead of the IN () subquery, you can execute INNER JOIN on c_1 OR c_2

 SELECT name FROM person /* Join against the other table on *either* column c_1 or c_2 */ INNER JOIN `table` ON `table`.c_1 = person.person_id OR `table`.c_2 = person.person_id WHERE /* And the WHERE condition only needs to be applied once */ c_1 = 3 OR c_2 = 3 

http://sqlfiddle.com/#!2/4d159/1