Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using a single common WHERE condition for UNION in SQL

Tags:

sql

mysql

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.

like image 382
jitendra Avatar asked Jul 01 '10 10:07

jitendra


People also ask

Can we use where condition in Union in SQL?

Using the Where Clause With the UNION OperatorWe can use the WHERE clause in either one or both of the SELECT statements to filter out the rows being combined. We can also use the WHERE clause in only one of the SELECT statements in the UNION.

Can you use both having and where clauses in one SQL statement?

You can create a WHERE clause and HAVING clause involving the same column. To do so, you must add the column twice to the Criteria pane, then specify one instance as part of the HAVING clause and the other instance as part of the WHERE clause.

Can we use UNION for same table in SQL?

The SQL UNION operator Put differently, UNION allows you to write two separate SELECT statements, and to have the results of one statement display in the same table as the results from the other statement.


2 Answers

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 individual ORDER BY's that the whole result is correctly ordered.

like image 57
Janick Bernet Avatar answered Oct 23 '22 17:10

Janick Bernet


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
like image 23
João Pereira Avatar answered Oct 23 '22 18:10

João Pereira