表连接注意事项

文章目录

• ​​表连接注意事项​​

• ​​准备`sql`​​
• ​​`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)`​​

表连接注意事项

准备​​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)​​

根据指定条件进行关联，符合条件的数据得到完整关联，并返回左表的所有数据。