Using a single common WHERE condition 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 condition applies to the second SELECT statement only. I need to somehow apply to both the SELECT. The only option I have now is to apply WHERE condition individually. But I am working with several UNIONs and it is kind of tedious to include WHERE in all the places. I was wondering if there is an easy way out.

By the way, I am working with MySQL.

Answer 1

There is no way around it, you have to repeat the WHERE for each individual select clause.

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

You could select into a temporary table, then select from the temporary table with the where clause.

Answer 4

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  
)
WHERE EnrollDate > '2010-01-01'  
ORDER BY EnrollDate

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

Answer 5

Another change is rather than joing location table with two table , Union student and teacher table in one query and than do the join with location table and than apply your where condition

select * from 
(
 select EnrollDate,ID,Name,Address from Location
 inner join 
 (
   SELECT a.date AS EnrollDate, a.id as Id, a.name as Name FROM student 
   union
   SELECT a.date AS EnrollDate, a.id as Id, a.name as Name FROM teacher 
 ) d on 
 d.id=Location.id
) WHERE EnrollDate >'2010-01-01'   
ORDER BY EnrollDate