I've been developing for years and through out the carrier I've been using only LEFT JOIN as it provides a more logical sense of mapping my one to many data.

However recently I ran across a performance thought:

Say I have table user with millions of rows and table gender with only 2 rows

user                                gender
=====================               ================
id name      genderId               id gender show
=====================               ================
1  Sam       1                      1  Male   0
2  Samantha  2                      2  Female 1
3  Another millions of records

If I'm doing

select * from user u left join gender g on u.genderId = g.id where g.show = 1;

Does SQL engine joins the whole million user records first, then scan the million rows of records for join projected value?

Or is the engine smart enough to query and join only the rows with correct gender? Or is RIGHT JOIN the correct method to provide best performance?

If the behavior is dependent on engine type, let's talk about MySQL InnoDB.

  • 2
    Show the EXPLAIN query output. Even if you have a gender.id pinned at 2, genderId in user may not be indexed, and even if it was it would be a large proportion of a secondary index, and you still need to look up name and id, so it might not be used. LEFT vs RIGHT won't matter at all. LEFT JOIN should be used when there isn't always RHS match, rather than just one to many, even a basic INNER JOIN does many to many.
    – danblack
    Commented Oct 19, 2018 at 1:59
  • 1
    Your join is INNER JOIN in practice, because a condition by right table presents in WHERE clause.
    – Akina
    Commented Oct 19, 2018 at 5:27
  • 2
    Your where condition on the outer joined table turns the outer join back into an inner join
    – user1822
    Commented Oct 19, 2018 at 6:40
  • DBMS engines are not smart enough to fetch only required entries. Bad news is that nobody is smart enough. Mankind is not able to operate sets at all. All we can do is to emulate the sets by lists and perform the search instead of fetch. Therefore the correct question should sound like that: "Why this query over that tables with that indexing and that cardinalities is slow?"
    – Kondybas
    Commented Oct 19, 2018 at 12:01
  • What do you want to happen when user.gender_id = 3 and there is no corresponding row in the table gender.
    – Rick James
    Commented Oct 22, 2018 at 4:16

1 Answer 1


Remember how SQL works: construct a set of tuples (rows) based on the given tables and joins, then filter those based on any where clause.
Because you have a where condition on the "right" table, your outer join is effectively converted into an inner join.

To retain the "outer-ness" of the join, put the filter condition into the join construct, rather than in the where clause:

select * 
from      user u 
left join gender g 
     on  u.genderId = g.id 
     and g.show = 1 
[where ...] 
  • that;s no a requirement of how it works, the planner is free to reorganise the query so long as the same set of results come out (modulo extensions like incomplete group bys)
    – Jasen
    Commented Oct 19, 2018 at 11:26
  • @Jasen By index hints we can force the planner to do exactly we want.
    – Kondybas
    Commented Oct 19, 2018 at 11:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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