七種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.在編譯的優化階段，如果左連接的結果集和內連接一樣時，左連接查詢會
轉換成內連接查詢，即編譯優化器認為內連接要比左連接高效。