七种SQL JOINS
七种SQL JOINS
-
1.SELECT select_list FROM TABLEA A LEFT JOIN TABLEB B ON A.Key=B.Key
-
2.SELECT select_list FROM TABLEA A RIGHT JOIN TABLEB B ON A.Key=B.Key
-
3.SELECT select_list FROM TABLEA A LEFT JOIN TABLEB B ON A.Key=B.Key
WHERE B.key is NULL -
4.SELECT select_list FROM TABLEA A RIGHT JOIN TABLEB B ON A.Key=B.key
WHERE A.Key is null -
5.SELECT select_list FROM TABLE A INNER JOIN TABLEB B ON A.Key=B.Key
-
6.SELECT select_list FROM TABLE A FULL OUTER JOIN TABLEB B ON A.Key=B.Key(Oracle支持)
-
7.SELECT select_list FROM TABLE A FULL OUTER JOIN TABLEB B ON
WHERE A.Key IS NULL OR B.Key IS NULL(Oracle支持)
运行效果
create table tbl_dept(
id int(11) not null auto_increment,
deptName varchar(30) default null,
locAdd varchar(40) default null,
primary key(id)
)engine=innodb auto_increment=1 default charset=utf8;
create table tbl_emp(
id int(11) not null auto_increment,
name varchar(20) default null,
deptId int(11) default null,
primary key(id),
key fk_dept_id (deptId)
#constraint fk_dept_id foreign key (deptId) references tbl_dept (id)
)engine=innodb auto_increment=1 default charset=utf8;
insert into tbl_dept(deptName,locAdd)values('RD',11);
insert into tbl_dept(deptName,locAdd)values('HR',12);
insert into tbl_dept(deptName,locAdd)values('MK',13);
insert into tbl_dept(deptName,locAdd)values('MIS',14);
insert into tbl_dept(deptName,locAdd)values('FD',16);
insert into tbl_emp(name,deptId)values('z2',1);
insert into tbl_emp(name,deptId)values('z3',1);
insert into tbl_emp(name,deptId)values('z4',1);
insert into tbl_emp(name,deptId)values('z4',1);
insert into tbl_emp(name,deptId)values('z6',1);
insert into tbl_emp(name,deptId)values('w5',2);
insert into tbl_emp(name,deptId)values('s7',3);
insert into tbl_emp(name,deptId)values('s8',4);
insert into tbl_emp(name,deptId)values('s9',51)
-
select * from tbl_emp;
-
select * from tbl_emp;
-
两个集合笛卡尓积 select * from tbl_emp,tbl_dept;
-
select * from tbl_emp a inner join tbl_dept b on a.deptId=b.id;
-
select * from tbl_emp a left join tbl_dept b on a.deptId=b.id;
-
select * from tbl_emp a right join tbl_dept b on a.deptId=b.id;
-
select * from tbl_emp a left join tbl_dept b on a.deptId=b.id where b.id is null;
-
select * from tbl_emp a right join tbl_dept b on a.deptId=b.id where a.deptId is null;
-
select * from tbl_emp a left join tbl_dept b on a.deptId=b.id
union
select * from tbl_emp a right join tbl_dept b on a.deptId=b.id;
-
select * from tbl_emp a left join tbl_dept b on a.deptId=b.id where b.id is null
union
select * from tbl_emp a right join tbl_dept b on a.deptId=b.id where a.deptId is null;
-
inner join 和 和 left join 的性能比较
从理论上来分析，确实是 inner join 的性能要好，因为是选出 2 个表都有
的记录，而 left join 会出来左边表的所有记录、满足 on 条件的右边表的记录。
1.在解析阶段，左连接是内连接的下一阶段，内连接结束后，把存在于左输
入而未存在于右输入的集，加回总的结果集，因此如果少了这一步效率应该要
高些。
2.在编译的优化阶段，如果左连接的结果集和内连接一样时，左连接查询会
转换成内连接查询，即编译优化器认为内连接要比左连接高效。
