文章目录
表连接注意事项
准备sql
drop table if exists `emp`;
create table if not exists `emp`(
`id` bigint(20) auto_increment not null comment '主键ID',
`name` varchar(100) not null comment '姓名',
`depId` bigint(20) not null comment '部门ID',
primary key (`id`)
)engine=innodb auto_increment=88 default charset=utf8 comment 'employees';
insert into `emp` (`name`,`depId`) values(
('lucy',99),
('jack',100),
('vogh',101)
);
drop table if exists `dep`;
create table if not exists `dep`(
`id` bigint(20) auto_increment not null comment '主键ID',
`name` varchar(100) not null comment '部门名称',
primary key (`id`)
) engine=innodb auto_increment=99 default charset=utf8 comment 'dep';
insert into `emp` (`name`) values(
('开发'),
('测试'),
('运维'),
);
select dep.*,emp.* from dep left join emp on dep.id = emp.depId
select dep.*,emp.* from dep left join emp on dep.id = emp.depId where emp.depId = 100
在连接完成之后进行筛选会筛选出符合条件的已完成关联的数据
select dep.*,emp.* from dep left join emp on (dep.id = emp.depId and dep.id = 100)
根据指定条件进行关联,符合条件的数据得到完整关联,并返回左表的所有数据。