can anybody suggest the Alternate of "intersect" & "Minus" for MYSQL?

Question

GOT error for the following query in MYSQL(version5.1)

SELECT year,month,sum(fact_1),sum(fact_2),sum(fact_3),sum(fact_4)
from(
select year,month,fact_1,fact_2,0 as fact_3,0 as fact_4 from table_1
intersect
select year,month,0 as fact_1,0 as fact_2,fact_3,fact_4 from table_2
) as combined_table
group by month,year

Error Line with code#1064:-

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select year,month,0 as fact_1,0 as fact_2,fact_3,fact_4 from table_2 ) as ct g' at line 5

but following query was giving desired Result:-

SELECT year,month,sum(fact_1),sum(fact_2),sum(fact_3),sum(fact_4)
from(
select year,month,fact_1 ,fact_2,0 as fact_3,0 as fact_4 from table_1
union
select year,month,0 as fact_1,0 as fact_2,fact_3,fact_4 from table_2
) as ct
group by month,year

Can anybody tell what error i am committing? can Anybody help me to understand the root cause behind the Problem.

Answer 1

MySQL doesn't support the INTERSECT keyword. Full syntax of SELECT for 5.1 is here:

http://dev.mysql.com/doc/refman/5.1/en/select.html

Answer 2

you can fake INTERSECT quite easily using an INNER (self) JOIN, this way you’ll only get rows from both resultsets:

    SELECT `a`.`id`, `a`.`name`
      FROM `a`
INNER JOIN `b`
     USING (`id`, `name`)

MINUS can be faked with a LEFT JOIN:

    SELECT DISTINCT `a`.`id`, `a`.`name`
      FROM `a`
 LEFT JOIN `b`
     USING (`id`, `name`)
     WHERE `b`.`id` IS NULL