Group by column and display count from all tables in mysql

Question

I have two tables: Table1 looks like this:

id   type    
1    bike     
2    car      
3    cycle
4    bike

And Table2 looks like this:

id   type    
1    bike     
2    car 

I want my final output to look like the following:

type   count_table1   count_table2
bike        2            1
car         1            1 
cycle       1            0

What is the most efficient way to do this in SQL?

Answer 1

Simple solution, no need for complicated table joins & functions:

SELECT type, MAX(count_table1) as count_table1, MAX(count_table2) as count_table2 FROM (
(
    SELECT type, COUNT(*) AS count_table1, 0 AS count_table2
    FROM Table1
    GROUP BY type
) UNION (
    SELECT type, 0 AS count_table1, COUNT(*) AS count_table2
    FROM Table2
    GROUP BY type)
) AS tmp
GROUP BY type

SQL Fiddle