How do MySQL joins really work?

Question

In my case joining table inside subquery or outside subquery gives very few difference with COUNT

CASE 1: about 6202 rows.
In this case table_c is joined inside subquery, but is only joined (No other actions for the table, not selecting any data or filtering whole query with that table).

CASE 2: about 6235 rows.
In this case that table is joined outside of subquery but joining key (groupCol) is the same.

Here is my self-explanatory MySQL query:

SELECT 
    COUNT(*)
FROM (
    SELECT
          a.*
        , b.some
        # no c columns
    # main table
    FROM table_a      AS a

    # no mention this
    LEFT JOIN table_b AS b
    ON a.col = b.bcol

    #### CASE 1 ###
    # this is left joined, this is matter
    # this is only joined, does nothing more
    LEFT JOIN table_c AS c
    ON c.col = a.groupCol
    #### CASE 1 ###

 WHERE 
    *** SOME statements *** 
    # c table not participating in WHERE, or GROUP-ing clause
 GROUP BY a.groupCol 
 ORDER BY a.dateCol
) AS subSelect 


#### CASE 2 : table_c is joined outside of subquery ###
LEFT JOIN table_c AS c
ON c.col = subSelect.groupCol
#### CASE 2 ###

WHERE 
    *** SOME statements *** 
    # table_c still not participating of course

I also know how does MySQL LEFT JOIN works: if data not found on second table, row corresponding fields from that table are set to null.

What what actually happens here I can't figure out.

Also case 1 is faster than case 2.
Why? Also for sure, that joining in case 1, contains least double amount of rows. In case 2 it's first filtered and then joined.
I thought this should be faster way...

Answer 1

I performed two queries in MySQL while using EXPLAIN.

The first one being the following:

select count(*) from parent
left outer join child on child.parent_id = id;

The second being the following:

select count(*) from (
    select * from parent p
    left outer join child c on c.parent_id = p.id
) count;

They both returned 68 records, which implies if you're getting something different, it is because of a filter you're using, not the way in which the query is being performed.

When I run explain on the first query I get the following:

'1', 'SIMPLE', 'parent', 'index', NULL, 'FK8338B25AD02D715A', '9', NULL, '15', 'Using index'
'1', 'SIMPLE', 'child', 'ref', 'PRIMARY,FKAFAB561391AEEB78', 'FKAFAB561391AEEB78', '8', 'db.parent.id', '2', 'Using index'

And when I run the second query I get the following:

'1', 'PRIMARY', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Select tables optimized away'
'2', 'DERIVED', 'p', 'ALL', NULL, NULL, NULL, NULL, '15', ''
'2', 'DERIVED', 'c', 'ref', 'PRIMARY,FKAFAB561391AEEB78', 'FKAFAB561391AEEB78', '8', 'db.p.id', '2', 'Using index'

The DERIVED merely means that I used aliases for the tables which were necessary in the second query in order to work properly. Otherwise, you'll notice they're very similar. You'll notice the only other major difference is an additional line "Select tables optimized away". This is an optimization which MySQL performs in order to reduce the queries and make it simpler.

In other words, the second query is only requiring MySQL to optimize in addition to the actual query, so it can only be slower and provide the same results as the first. If your count is not consistent between these two queries, it is likely because something else is going on which is probably a filter of some sort.

Hope that helps!

Answer 2

After taking 20 min break I've found out what's going on:

In first case when joining inside the subquery, GROUP-ing done the job, but when joining outside it would be GROUP-ed again, sure that would be stupid but...

Seems I've got duplicate entries to table_c that is multiplying some of rows.

Also thank you @Neil