Union Query format in mysql I am training in MySQL.
I have database called ships. My goal is => For each class, define the number of ships of this class that were sunk in battles. 
Result set: class, number of sunked ships.
Here are the tables from which I wanna get the result:


I tried to resolve my problem in this way:

SELECT classes.class, COUNT(outcomes.ship) as sunks
FROM classes LEFT JOIN outcomes ON classes.class=outcomes.ship
WHERE outcomes.result='sunk'
UNION
SELECT ships.class, COUNT(outcomes.ship) as sunks
                FROM ships
                LEFT JOIN outcomes ON ships.name=outcomes.ship
                WHERE outcomes.result='sunk'


The result set of my query is just: Bismark and Kogo classes but other classes were gone.

But it was not exactly what I wanted to get.
I want to get all classes - even with zero outcomes.result.

What am I going to do to make it works properly?
Here is the table script just in case: ships
First problem is that you "override the LEFT join by using the WHERE clause referencing that table, so no "NULL" row can show in the results.

Second problem is that you are using an aggregate (count()) but you forgot to add a proper GROUP BY.

Fixing these the query looks like this:

SELECT class, count(ship) as sunks
FROM (
  ( 
   SELECT classes.class, outcomes.ship
   FROM classes
   LEFT JOIN outcomes ON classes.class=outcomes.ship AND outcomes.result='sunk'
  )
  UNION
  (
   SELECT ships.class, outcomes.ship
   FROM ships
   LEFT JOIN outcomes ON ships.name=outcomes.ship AND outcomes.result='sunk'
  )
) AS tmp
GROUP BY class


There is a fiddle based on your script to see it in action http://sqlfiddle.com/#!9/4840f6/8