MySQL: Combining the results of 2 columns in one statement

Question

Lets just say I have 2 result sets as follows

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

I need to combine these result into a single SET, So I'd have something like this:

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

Currently, I'm doing this with a a UNION like so:

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 performing the same operation twice on the table, Just retrieving different rows each time. Is there a more efficient way I could go about doing this? I need the result in a single column since that's a limitation of IN. In the long run, what I need to do is 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 go about finding all of this? Any and all help is welcome.

Answer 1

Instead of the IN () subquery, you can perform an INNER JOIN on either 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