How do I apply a WHERE statement after a UNION in SQL/MySQL?
Asked
Active
Viewed 1.5e+01k times
3 Answers
166
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.
Jonathan Leffler
- 730,956
- 141
- 904
- 1,278
-
3Any idea if MySQL will push the predicate into the individual statements? – Martin Smith Mar 27 '11 at 20:41
-
2@Martin: it might - it might not. You'd have to look at the query plan. It also depends on the WHERE clause conditions. – Jonathan Leffler Mar 27 '11 at 20:43
-
3@Martin, Don't hope too much. Having us to do something like this shows just how dumb MySQL is.... – Pacerier May 05 '15 at 08:03
-
works flawlessly! – Gaurav Mar 25 '22 at 14:03
27
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.
Fabrício Matté
- 69,329
- 26
- 129
- 166
David
- 208,112
- 36
- 198
- 279
0
select column1..... from table1
where column1=''
union
select column1..... from table2
where column1= ''
Ruby Racer
- 5,690
- 1
- 26
- 43
pinkykitty
- 35
- 1