I have the following tables:

```
create temporary table Items (item_id int, item_name varchar(10));
create temporary table ItemRating (item_id int, rating int);

```

With the following data:

```
insert into Items (item_id, item_name) values (1,'Item 1'),(2,'Item 2'),(3,'Item 3'),(4,'Item 4'),(5,'Item 5');
insert into ItemRating values (1,9),(1,6),(3,10);

```

And I run the following query:

```
select i.item_id, i.item_name, avg(ir.rating) from Items i left join ItemRating ir ON ir.item_id = i.item_id group by ir.item_id;

```

This is the result I get:

```
+---------+-----------+----------------+
| item_id | item_name | avg(ir.rating) |
+---------+-----------+----------------+
|       2 | Item 2    |           NULL |
|       1 | Item 1    |         7.5000 |
|       3 | Item 3    |        10.0000 |
+---------+-----------+----------------+

```

Now, I fully understand that the query is written wrong, what I want is to be doing a group by on i.item\_id. But I don't understand the behavior. Why does MYSQL display item\_id 2 in the results, but not 4 or 5? I would actually expect to see only items 1 & 3 because they're the only ones with a corresponding record in ItemRating.

So, can anyone explain to me what MYSQL is doing here?