MySql（九）--七种JOIN

一、理论

 

二、编写

2.1 建表语句

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(30) 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',15);

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('Z5',1);
insert into tbl_emp(name,deptId) VALUES('w5',2);
insert into tbl_emp(name,deptId) VALUES('w6',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);

2.2 查询语句

inner join

left join

right join

 

full outer join

mysql不支持这种语法。oracle支持。

用一种变通的方式来实现：