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:

        , 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 ###

    *** 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 ###

    *** 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...


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!

  • Thank you very much, for the deep explanatory answer. You are right, neither joining nor filtering is not problem... – George Garchagudashvili Dec 20 '13 at 12:12
  • @GeorgeGarchagudashvili Glad to have shed some light on the issue. I often wonder such things myself, but you never know for sure until you test it. – Neil Dec 20 '13 at 12:31
  • Cheers bro. You are 100% right. Thank you very much for your time and attention. Much appreciated – George Garchagudashvili Dec 20 '13 at 12:44

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.