WHERE statement after a UNION in SQL?

MysqlSql

Mysql Problem Overview

How do I apply a WHERE statement after a UNION in SQL/MySQL?

Mysql Solutions

Solution 1 - Mysql

If you want to apply the WHERE clause to the result of the UNION, then you have to embed the UNION in the FROM clause:

SELECT *
  FROM (SELECT * FROM TableA
        UNION
        SELECT * FROM TableB
       ) AS U
 WHERE U.Col1 = ...

I'm assuming TableA and TableB are union-compatible. You could also apply a WHERE clause to each of the individual SELECT statements in the UNION, of course.

Solution 2 - Mysql

You probably need to wrap the UNION in a sub-SELECT and apply the WHERE clause afterward:

SELECT * FROM (
    SELECT * FROM Table1 WHERE Field1 = Value1
    UNION
    SELECT * FROM Table2 WHERE Field1 = Value2
) AS t WHERE Field2 = Value3

Basically, the UNION is looking for two complete SELECT statements to combine, and the WHERE clause is part of the SELECT statement.

It may make more sense to apply the outer WHERE clause to both of the inner queries. You'll probably want to benchmark the performance of both approaches and see which works better for you.

Solution 3 - Mysql

select column1..... from table1
where column1=''
union
select column1..... from table2
where column1= ''
Categories
Recommended Mysql Solutions
Recommended Sql Solutions

Previous Solution:

Custom installed font not displayed correctly in UILabel

IosIos4FontsUilabelUifont

Next Solution:

Javascript date regex DD/MM/YYYY

JavascriptRegex

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestioneinsteinView Question on Stackoverflow
Solution 1 - MysqlJonathan LefflerView Answer on Stackoverflow
Solution 2 - MysqlDavidView Answer on Stackoverflow
Solution 3 - MysqlpinkykittyView Answer on Stackoverflow