Code Sample, a copy-pastable example if possible

pd.read_sql(
            '''
            SELECT CountryCode, ShortName,LatestPopulationCensus
            FROM Country
            WHERE Region = 'South Asia'

            UNION

            SELECT CountryCode, LatestPopulationCensus, LongName
            FROM Country
            WHERE Region = 'North America'
            '''
    ,con=conn)

Problem description

I noticed that when doing UNION of two datasets. The result depends on the order of selected columns and unites data according to this order and not to underlying labels. That doesn't look right.

CountryCode ShortName LatestPopulationCensus 0 AFG Afghanistan 1979 1 BGD Bangladesh 2011 2 BMU 2010 The Bermudas 3 BTN Bhutan 2005 4 CAN 2011 Canada 5 IND India 2011

Is there a way to correct it withing pandas or is it SQL issue?

Note: We receive a lot of issues on our GitHub tracker, so it is very possible that your issue has been posted before. Please check first before submitting so that we do not have to handle and close duplicates!

Note: Many problems can be resolved by simply upgrading pandas to the latest version. Before submitting, please check if that solution works for you. If possible, you may want to check if master addresses this issue, but that is not necessary.

For documentation-related issues, you can check the latest versions of the docs on master here:

https://pandas-docs.github.io/pandas-docs-travis/

If the issue has not been resolved there, go ahead and file it in the issue tracker.

Expected Output

Output of pd.show_versions()

[paste the output of ``pd.show_versions()`` here below this line]

Comment From: jorisvandenbossche

Your question seems to be one about SQL and not pandas? And anyway, general usage questions are better asked at eg StackOverflow.