How to select distinct values from 2 tables with sort in one query?

I have

• table1 : country, theOrderColumn1
• table2 : country, theOrderColumn2

I want to join DISTINCT country from these two SELECT statements:

SELECT DISTINCT `country` FROM `table1` ORDER BY `theOrderColumn1`

and

SELECT DISTINCT `country` FROM `table2` ORDER BY `theOrderColumn2`

Example:

table1 (country, theOrderColumn1): (uk, 1), (usa, 2)

table2 (c开发者_开发问答ountry, theOrderColumn2): (france, 1), (uk, 2)

I want this result:

france
uk
usa

select distinct country
from (
    select country, theOrderColumn from table1
    union all
    select country, theOrderColumn from table2
) a 
order by theOrderColumn

select country, theOrderColumn from (
select distinct t1.country as country, t1.theOrderColumn as theOrderColumn from table t1
union
select distinct t2.country as country, t2.theOrderColumn as theOrderColumn from table t2) t3
order by theOrderColumn

If you want to preserve order given by theOrderColumn1 and theOrderColumn2 at the same time, you can use the column index to specify the ORDER BY column.

SELECT DISTINCT country FROM (
(
    SELECT country AS c, theOrderColumn1 AS d FROM table1
    UNION
    SELECT country AS c, theOrderColumn2 AS d FROM table2
) ORDER BY 2)

Take a look at the answers to this question: SQL Query - Using Order By in UNION

select a.country,a.theOrderColumn
(
select country,theOrderColumn
from table1
union
select country,theOrderColumn
from table2
) a
order by a.theOrderColumn

Though you will get duplicates if theOrderColumn is different in table 1 and table 2.

It depends on what you are wanting and how you want to join the two tables together. If you are joining based on "theOrderColumn", then the query would be

SELECT DISTINCT country 
FROM table1 
JOIN table2 ON table1.theOrderColumn = table2.theOrderColumn 
ORDER BY theOrderColumn

If you want to join across country (which wouldn't make sense as the country would be identical in both tables) then you could swap out "country" in the join clause.

Also, depending on your SQL dialect spoken by your DBMS, your mileage may vary with the above query. Can you clarify more of what you are after?