SQL中join on条件和where条件的区别

SQL中join on条件和where条件的区别

简单的说join on条件是对临时表横向（列级别）或纵向（行级别）数据的增加；而where条件则是对临时表中纵向（行级别）数据的过滤。

用代码说话

####################
## 对数据库表的初始化
####################
create table tbl_user (
    id int auto_increment,
    name varchar(32) not null,
    group_id int not null,

    primary key(id),
    index (group_id)
) comment='用户表';

create table tbl_group (
    id int auto_increment,
    name varchar(32) not null,

    primary key(id)
)comment='分组表';

insert into tbl_group (name) values('group');
insert into tbl_user (name, group_id) values('user01', 1);
insert into tbl_user (name, group_id) values('user02', 1);

###########################################################
## ① 通过SQL验证join on条件可以对临时表进行横向（列级别）数据的增加
###########################################################
select u.id as u_id, u.name as u_name, u.group_id as u_group_id, g.id as g_id, g.name as g_name
from tbl_user as u
    left join tbl_group as g
        on g.id = u.group_id;

+------+--------+------------+------+--------+
| u_id | u_name | u_group_id | g_id | g_name |
+------+--------+------------+------+--------+
|    1 | user01 |          1 |    1 | group  |
|    2 | user02 |          1 |    1 | group  |
+------+--------+------------+------+--------+

#############################################################
## ② 通过SQL查询验证join on条件可以对临时表进行纵向（行级别）数据增加
#############################################################
select g.id as g_id, g.name as g_name, u.id as u_id, u.name as u_name, u.group_id as u_group_id
from tbl_group as g
    left join tbl_user as u
        on g.id = u.group_id;

+------+--------+------+--------+------------+
| g_id | g_name | u_id | u_name | u_group_id |
+------+--------+------+--------+------------+
|    1 | group  |    1 | user01 |          1 |
|    1 | group  |    2 | user02 |          1 |
+------+--------+------+--------+------------+

######################################################
## ③ 通过SQL查询验证where条件对临时表纵向（行级别）数据的过滤
######################################################
select g.id as g_id, g.name as g_name, u.id as u_id, u.name as u_name, u.group_id as u_group_id
from tbl_group as g
    left join tbl_user as u
        on g.id = u.group_id
where u.id = 1;

+------+--------+------+--------+------------+
| g_id | g_name | u_id | u_name | u_group_id |
+------+--------+------+--------+------------+
|    1 | group  |    1 | user01 |          1 |
+------+--------+------+--------+------------+