As example I'll use sakila sample database. Our objective is to get a list of movies with the number of actors which first or last name starts with letter a. First lets check how many movies that meet that condition is there.

SELECT
            count( DISTINCT film_id)
    FROM
        film LEFT JOIN film_actor using (film_id)
        LEFT JOIN actor using (actor_id)
    WHERE
        first_name like 'a%'
        OR last_name LIKE 'a%'

Now we know that in 407 films appeared the definite actor. So now lets list them:

SELECT
            film_id,   title,   count( actor_id )
    FROM
        film LEFT JOIN film_actor using (film_id)
        LEFT JOIN actor using (actor_id)
    WHERE
        first_name like 'a%'
        OR last_name LIKE 'a%'
    GROUP BY
        film_id
    ORDER BY
         1 ASC

The result looks like this:

Result of 3 joined tables with a global condition

All right, we got list of all movies that has at least one specific actor, but what if we want list of all movies with the number of actors even if the number is 0? We have to modify our query.

SELECT
            film_id,   title,   IFNULL(count, 0)
    FROM
        film LEFT JOIN ( SELECT
            film_id,
                 count(  actor_id) as count
         FROM
             film_actor LEFT JOIN actor using (actor_id)
        WHERE
            first_name like 'a%' OR   last_name LIKE 'a%'
        GROUP BY
            film_id) as act using (film_id)
    ORDER BY
         1 ASC

Now that's more like it:

Result of join with subquery and local condition

The the difference is clear but how does it work? Lets split this statement into two separate queries:
  • first one is simply a list of films with no conditions

    SELECT
                film_id,   title
        FROM
            film
        ORDER BY
             1 ASC
    

    Probably you could imagine the result yourself:

    Result of SELECT title FROM film
  • second is quite similar to the query that gave as a result list of 407 movies with specific actors:

    SELECT
                film_id,   count( actor_id ) as count
        FROM
            film_actor LEFT JOIN actor using (actor_id)
        WHERE
            first_name like 'a%'
            OR last_name LIKE 'a%'
        GROUP BY
            film_id
    

    Adding a title we could get a result identical to the result of first query:

    Result of SELECT film_id, count(actor_id) as count...

How many rows do we get as a result of this query? Any guess? Lets check.

SELECT
            count( distinct film_id)
    FROM
        film_actor LEFT JOIN actor using (actor_id)
    WHERE
        first_name like 'a%'
        OR last_name LIKE 'a%'

407 rows. I've to say I was suprised by the result.

So basically what we are doing in the correct query is a selecting the list of all film, and than joining to each of those films (using a film_id column) a number of actors. What if there is no actors' number? We are using LEFT JOIN, so if it is empty we are attaching null value. If we were using INNER JOIN we wouldn't get this row at all. On the image below you can see what will be joined to each titles.

Titles and suiting actor numbers

First query was creating a list of titles with actors' number and that filtering result using WHERE clause, removing all rows that don't meet the conditions (that is those who had no specific actors - 0 as a number).

The most important thing in this axample was a changing a scope of WHERE condition from "global" - affecting all 3 joined tables into "local" - filtering only this part of result that will be later attached into outer query result