虽然这类资料比较多....我觉得还是有必要记下来,新手可以看看吧。。。老司机可以一眼飘过那。。。

常用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;