SQL: difference between where in main body vs join clause

Question

I'm wondering why does the following queries give me a slightly different dataset:

SELECT t.name, COUNT(e.id)
FROM event_type t
LEFT JOIN event e ON t.id = e.type_id AND e.start BETWEEN ? AND ?
GROUP BY t.name;

SELECT t.name, COUNT(e.id)
FROM event_type t
LEFT JOIN event e ON t.id = e.type_id
WHERE e.start BETWEEN ? AND ?
GROUP BY t.name;

So I just moved BETWEEN clause to the main body, logically, it does not matter where to apply it, but the result says it matters. Any suggestions? Thanks!

UPD: tried on MySQL 5.6

create table event_type
(
    id       int auto_increment primary key,
    name     varchar(100) not null,
    constraint UNIQ_93151B825E237E06 unique (name)
) collate = utf8_unicode_ci;


create table event
(
    id                         int auto_increment primary key,
    type_id                    int                  null,
    start                      datetime             not null,
    ...

    constraint FK_3BAE0AA7C54C8C93
        foreign key (type_id) references event_type (id)
) collate = utf8_unicode_ci;

create index IDX_3BAE0AA7C54C8C93
    on event (type_id);

Answer 1

Maybe it's hard to answer this question without some images! but I try.

Let's assume this is the event_type table

Id	Name
1	First
2	Second

Events table:

Id	TypeId	Start
5	1	2022-10-01
6	1	2022-10-10

So for this query:

SELECT t.name, COUNT(e.id)
FROM event_type t
LEFT JOIN event e ON t.id = e.type_id AND e.start BETWEEN '2022-10-01' AND '2022-10-05'
GROUP BY t.name;

The result will be:

Name	Count(e.id)
First	1
Second	0

But why? becuase sql engine when try to get result on left join, it will check both of id and start, actually the result of prevois query is like this:

Id	Name	Id	TypeId	Start
1	First	5	1	2022-10-01
2	Second	null	null	null

That's it! When you try to use Between in where clause, in fact you are filtering the null values so sql would ingore them and the final result would be different.

I hope it's clear enough!