MySQL: I wonder why the inner join occurs between no-relation column

Question

In my Query, There are two conditions named "name", and this column exists in the table milestone and user, respectively.

select distinct issue.id from issue 
inner join user on issue.user_id=user.id 
left outer join milestone on issue.milestone_id=milestone.id 
where issue.is_opened=true and user.name='aaa' and milestone.name='bbb';

I analyze this query using "Explain Analyze Option" and the results are as follows.

1.-> Nested loop inner join  (cost=5484.47 rows=1004) (actual time=7.410..7.819 rows=17 loops=1)
   2.-> Nested loop inner join  (cost=0.70 rows=1) (actual time=0.887..0.897 rows=1 loops=1)
      3.-> Covering index lookup on user1_ using idx_name (name='aaa')  (cost=0.35 rows=1) (actual time=0.854..0.858 rows=1 loops=1)
      4.-> Covering index lookup on milestone2_ using idx_m_name (name='bbb')  (cost=0.35 rows=1) (actual time=0.027..0.034 rows=1 loops=1)
   5.-> Filter: ((issue0_.milestone_id = milestone2_.id) and (issue0_.is_opened = true))  (cost=1567.42 rows=1004) (actual time=6.522..6.919 rows=17 loops=1)
      6.-> Index lookup on issue0_ using fk_issue_user_idx (user_id=user1_.id)  (cost=1567.42 rows=40168) (actual time=6.509..6.892 rows=200 loops=1)

I wonder how two unrelated tables perform nested loop inner join.(at line 2) because there was no related column between two table. If it was marked as outer join, I would have understood that the columns would simply be combined. But I don't understand that it's marked as Inner.

Does this answer your question? [Left Join With Where Clause](https://stackoverflow.com/questions/4752455/left-join-with-where-clause) — philipxy, Oct 28 '22 at 17:23
The query is malformed. The `WHERE` clause silently converts the outer join into an inner join. Please fix the query first, then we can tackle performance. — The Impaler, Oct 28 '22 at 17:43

score 1 · Answer 1 · answered Oct 28 '22 at 16:54

It's marked as inner because you have a condition milestone.name='bbb' in your WHERE clause.

In an outer join, joined rows that have no match in the milestone table will contain all NULLs in the columns of milestone. But your condition would eliminate all those rows, because milestone.name='bbb' must be non-NULL. Only the rows that do have a match in the milestone table can be included in the query result. Therefore the outer join is effectively an inner join.

This behavior is documented here:

https://dev.mysql.com/doc/refman/8.0/en/outer-join-simplification.html

MySQL converts the query to a query with no outer join operation if the WHERE condition is null-rejected. (That is, it converts the outer join to an inner join.) A condition is said to be null-rejected for an outer join operation if it evaluates to FALSE or UNKNOWN for any NULL-complemented row generated for the operation.

MySQL: I wonder why the inner join occurs between no-relation column

1 Answers1