CODEWITHSUNDEEP
mysqlunionunion-all
Lemures
Lemures

Reputation: 482

Select union with where condition while columns have different names

Scenario:

Table1:

city_id     |   city_name       | ...
-------------------------------------
1           |   Aaaa
2           |   Bbb
3           |   Ccc
4           |   Ddd
...

Table2:

region_id   |   region_name     | ...
-------------------------------------
1001        |   Qwer
1002        |   Zxcv
1003        |   Vbnm
...

Expected result:

id          |   name     | ...
-------------------------------------
3           |   Ccc
4           |   Ddd
1001        |   Qwer
1002        |   Zxcv
1003        |   Vbnm
...

The IDs are guaranteed to be different accross 2 tables.

I want to select both tables with union (as they were 1 single table) and then use WHERE

query1

SELECT
    city_id     AS 'id',
    city_name   AS 'name'
FROM table1
UNION
SELECT
    region_id   AS 'id',
    region_name AS 'name'
FROM table2
WHERE 'id' > 2

But this query returns only data from 1st table.

I just saw that one way to achieve this is to wrap the union in a select.

query2

SELECT * FROM (
    SELECT
        city_id     AS 'id',
        city_name   AS 'name'
    FROM table1
    UNION
        region_id   AS 'id',
        region_name AS 'name'
    FROM table2
) AS t
WHERE t.id > 2

Is there any soliton without using subquery?

If not, how is performance affected when using subquery in this case (approximately)?

Bonus question

In query2, why is WHERE applied to the first table? Shouldn't it be something like:

{{
    SELECT
        city_id     AS 'id',
        city_name   AS 'name'
    FROM table1
}}
UNION
{{
    SELECT
        region_id   AS 'id',
        region_name AS 'name'
    FROM table2
    WHERE 'id' > 2
}}

update

Comparing subquery and the accepted answer

Upvotes: 0

Views: 38

Answers (1)

Flavian-Calinic Stuparu
Flavian-Calinic Stuparu

Reputation: 199

You could add the same WHERE clause to both queries :

SELECT
    city_id     AS 'id',
    city_name   AS 'name'
FROM table1
WHERE city_id > 2
UNION
    region_id   AS 'id',
    region_name AS 'name'
FROM table2
WHERE region_id > 2
;

As for your question related to performance, if the WHERE clause is evaluated after the UNION subquery is evaluated and there are indexes on the id columns, you may not benefit from the the indexes. But you must check the execution plan to see if the optimizer identifies this case correctly and goes by the index or not.

Upvotes: 2

Related Questions