Using a single WHERE clause for UNION in SQL

Question

I am trying to do something like this:

SELECT a.date AS EnrollDate, a.id, a.name, b.address FROM student a JOIN Location b ON a.id=b.id UNION SELECT a.date AS EnrollDate, a.id, a.name, b.address FROM teacher a JOIN Location b ON a.id=b.id WHERE a.date>'2010-01-01' ORDER BY EnrollDate 

But the WHERE clause applies only to the second SELECT statement. I need to apply SELECT somehow. The only option I have now is to apply the WHERE clause individually. But I work with several UNIONs, and it's pretty tedious to include WHERE in all places. I was wondering if there is a simple way out.

By the way, I work with MySQL.

Answer 1

 SELECT * FROM ( SELECT a.date AS EnrollDate, a.id, a.name, b.address FROM student a JOIN Location b ON a.id=b.id UNION SELECT a.date AS EnrollDate, a.id, a.name, b.address FROM teacher a JOIN Location b ON a.id=b.id ) A WHERE EnrollDate > '2010-01-01' ORDER BY EnrollDate 

This also has the advantage, compared to the individual ORDER BY , that the whole result is correctly ordered.

Answer 2

Have you tried something like:

 SELECT * FROM ( SELECT a.date AS EnrollDate, a.id, a.name, b.address FROM student a JOIN Location b ON a.id=b.id UNION SELECT a.date AS EnrollDate, a.id, a.name, b.address FROM teacher a JOIN Location b ON a.id=b.id ) A WHERE a.date>'2010-01-01' ORDER BY EnrollDate 

Answer 3

There is no way around this, you need to repeat WHERE for each individual select clause.