在多表join时where和on关键字的问题

先来举个关于where过滤的例子

下面是两个代码段：

select
    stu.id,
    stu.name,
    cls.teacher
from (
    select
        id,
        name,
        class_id
    from stu
    -- 这里不同
    where class_id=1
)stu
left join (
    select 
        teacher,
        id
    from cls
)cls
on stu.class_id=cls.id

select
    stu.id,
    stu.name,
    cls.teacher
from (
    select
        id,
        name,
        class_id
    from stu
)stu
left join (
    select 
        teacher,
        id
    from cls
)cls
-- 这里不同
where class_id=1
on stu.class_id=cls.id

可以明确的说两者结果没有不同，但是代码一明显优于代码二，这就是我们所说的谓词下推带来的优点：它可以先将需要的数据进行过滤出来再join，这样处理的数据量明显减少。

还有一个是在join中，on与where的用法：

同样是上面的例子

select
    stu.id,
    stu.name,
    cls.teacher
from (
    select
        id,
        name,
        class_id
    from stu
    where class_id=1
)stu
left join (
    select 
        teacher,
        id
    from cls
)cls
-- 这里不同
on stu.class_id=cls.id
where teacher='xxx'

select
    stu.id,
    stu.name,
    cls.teacher
from (
    select
        id,
        name,
        class_id
    from stu
    where class_id=1
)stu
left join (
    select 
        teacher,
        id
    from cls
)cls
-- 这里不同
where stu.class_id=cls.id 
and teacher='xxx'

同样，以上的代码也是相同的结果，也是代码一要优于代码二，因为join中先执行on进行条件连接，没有on关键词默认笛卡尔积，两表行数直接相乘，再通过where过滤出需要的数据来，这样会导致数据量倍增，效率低下，因此连接条件还是要通过on来确定。