How to get the distinct field value in a multiple field in multiple table?

Question

Hi friends,

i have a two table x and y. In that x table has x1 as country, x2 as country, y table has y1 as country, y2 has country. For this data, how can i get the distinct country values in this two table with this four country field?

Before that i used a single country in a single table like this,

$query="select distinct(`x1`) from x";

Now, i have the take distinct values in this two table as well as from four country values. For this union is possible. But i need any solution used in a single query without union concept.

Please provide the idea for this. Thanks in Advance...

Answer 1

Idea: use UNION

Answer 2

not the most efficient query ever, but try this:

SELECT `x1` FROM x
UNION 
SELECT `x2` FROM x
UNION 
SELECT `y1` FROM y
UNION 
SELECT `y2` FROM y

Answer 3

Union is the best answer to this question, as that's exactly what it's designed for.

The only other method I can think of would be a full outer join - something like:

select coalesce(cx.country,cy.country) country 
from (select distinct country from x) cx
full outer join (select distinct country from y) cy 
on cx.country = cy.country

However, you can't do full outer joins in MYSQL (as far as I know) - and the workarounds involve unions...