终于找到一个有助理解left/right/full outer join的例子
来源：互联网 发布：好听的淘宝收件人名字 编辑：程序博客网 时间：2024/05/20 11:24
近日在学习《Understading DB2》的时候找到了一个例子，对于理解 left/right/full 三种 outer join 的大有裨益。
先看样本数据，来自DB2的示例数据库 sample：
db2 => insert into employee values('99999','killkill','N','Huang',null,null,null,'nothing',16,'M',null,null,null,null) ; DB20000I The SQL command completed successfully. db2 => SELECT empno, firstnme, lastname, workdept | db2 => SELECT deptno, deptname from department order by 1;db2 (cont.) => FROM employee order by 4; | | EMPNO FIRSTNME LASTNAME WORKDEPT | DEPTNO DEPTNAME ------ ------------ --------------- -------- | ------ ------------------------------------ 000010 CHRISTINE HAAS A00 | A00 SPIFFY COMPUTER SERVICE DIV. 000110 VINCENZO LUCCHESSI A00 | B01 PLANNING 000120 SEAN O'CONNELL A00 | C01 INFORMATION CENTER 200010 DIAN HEMMINGER A00 | D01 DEVELOPMENT CENTER 200120 GREG ORLANDO A00 | D11 MANUFACTURING SYSTEMS 000020 MICHAEL THOMPSON B01 | D21 ADMINISTRATION SYSTEMS 000030 SALLY KWAN C01 | E01 SUPPORT SERVICES 000130 DELORES QUINTANA C01 | E11 OPERATIONS 000140 HEATHER NICHOLLS C01 | E21 SOFTWARE SUPPORT 200140 KIM NATZ C01 | F22 BRANCH OFFICE F2 000060 IRVING STERN D11 | G22 BRANCH OFFICE G2 000150 BRUCE ADAMSON D11 | H22 BRANCH OFFICE H2 000160 ELIZABETH PIANKA D11 | I22 BRANCH OFFICE I2 000170 MASATOSHI YOSHIMURA D11 | J22 BRANCH OFFICE J2 000180 MARILYN SCOUTTEN D11 | 000190 JAMES WALKER D11 | 14 record(s) selected. 000200 DAVID BROWN D11 |000210 WILLIAM JONES D11 |000220 JENNIFER LUTZ D11 |200170 KIYOSHI YAMAMOTO D11 |200220 REBA JOHN D11 |000070 EVA PULASKI D21 |000230 JAMES JEFFERSON D21 |000240 SALVATORE MARINO D21 |000250 DANIEL SMITH D21 |000260 SYBIL JOHNSON D21 |000270 MARIA PEREZ D21 |200240 ROBERT MONTEVERDE D21 |000050 JOHN GEYER E01 |000090 EILEEN HENDERSON E11 |000280 ETHEL SCHNEIDER E11 |000290 JOHN PARKER E11 |000300 PHILIP SMITH E11 |000310 MAUDE SETRIGHT E11 |200280 EILEEN SCHWARTZ E11 |200310 MICHELLE SPRINGER E11 |000100 THEODORE SPENSER E21 |000320 RAMLAL MEHTA E21 |000330 WING LEE E21 |000340 JASON GOUNOT E21 |200330 HELENA WONG E21 |200340 ROY ALONZO E21 |99999 killkill Huang - |注意，我故意插了一条没有对应部门的测试数据到 employee 表中，现在看看 left/right/full out join 的结果：
db2 => SELECT empno, firstnme, lastname, deptnamedb2 (cont.) => FROM employee LEFT OUTER JOIN departmentdb2 (cont.) => ON workdept = deptnodb2 (cont.) => ; EMPNO FIRSTNME LASTNAME DEPTNAME ------ ------------ --------------- ------------------------------------000010 CHRISTINE HAAS SPIFFY COMPUTER SERVICE DIV. 000020 MICHAEL THOMPSON PLANNING 000030 SALLY KWAN INFORMATION CENTER 000050 JOHN GEYER SUPPORT SERVICES 000060 IRVING STERN MANUFACTURING SYSTEMS 000070 EVA PULASKI ADMINISTRATION SYSTEMS 000090 EILEEN HENDERSON OPERATIONS 000100 THEODORE SPENSER SOFTWARE SUPPORT 000110 VINCENZO LUCCHESSI SPIFFY COMPUTER SERVICE DIV. 000120 SEAN O'CONNELL SPIFFY COMPUTER SERVICE DIV. 000130 DELORES QUINTANA INFORMATION CENTER 000140 HEATHER NICHOLLS INFORMATION CENTER 000150 BRUCE ADAMSON MANUFACTURING SYSTEMS 000160 ELIZABETH PIANKA MANUFACTURING SYSTEMS 000170 MASATOSHI YOSHIMURA MANUFACTURING SYSTEMS 000180 MARILYN SCOUTTEN MANUFACTURING SYSTEMS 000190 JAMES WALKER MANUFACTURING SYSTEMS 000200 DAVID BROWN MANUFACTURING SYSTEMS 000210 WILLIAM JONES MANUFACTURING SYSTEMS 000220 JENNIFER LUTZ MANUFACTURING SYSTEMS 000230 JAMES JEFFERSON ADMINISTRATION SYSTEMS 000240 SALVATORE MARINO ADMINISTRATION SYSTEMS 000250 DANIEL SMITH ADMINISTRATION SYSTEMS 000260 SYBIL JOHNSON ADMINISTRATION SYSTEMS 000270 MARIA PEREZ ADMINISTRATION SYSTEMS 000280 ETHEL SCHNEIDER OPERATIONS 000290 JOHN PARKER OPERATIONS 000300 PHILIP SMITH OPERATIONS 000310 MAUDE SETRIGHT OPERATIONS 000320 RAMLAL MEHTA SOFTWARE SUPPORT 000330 WING LEE SOFTWARE SUPPORT 000340 JASON GOUNOT SOFTWARE SUPPORT 200010 DIAN HEMMINGER SPIFFY COMPUTER SERVICE DIV. 200120 GREG ORLANDO SPIFFY COMPUTER SERVICE DIV. 200140 KIM NATZ INFORMATION CENTER 200170 KIYOSHI YAMAMOTO MANUFACTURING SYSTEMS 200220 REBA JOHN MANUFACTURING SYSTEMS 200240 ROBERT MONTEVERDE ADMINISTRATION SYSTEMS 200280 EILEEN SCHWARTZ OPERATIONS 200310 MICHELLE SPRINGER OPERATIONS 200330 HELENA WONG SOFTWARE SUPPORT 200340 ROY ALONZO SOFTWARE SUPPORT 99999 killkill Huang -
db2 => SELECT empno, firstnme, lastname, deptnamedb2 (cont.) => FROM employee RIGHT OUTER JOIN departmentdb2 (cont.) => ON workdept = deptnodb2 (cont.) => ; EMPNO FIRSTNME LASTNAME DEPTNAME ------ ------------ --------------- ------------------------------------000010 CHRISTINE HAAS SPIFFY COMPUTER SERVICE DIV. 000020 MICHAEL THOMPSON PLANNING 000030 SALLY KWAN INFORMATION CENTER 000050 JOHN GEYER SUPPORT SERVICES 000060 IRVING STERN MANUFACTURING SYSTEMS 000070 EVA PULASKI ADMINISTRATION SYSTEMS 000090 EILEEN HENDERSON OPERATIONS 000100 THEODORE SPENSER SOFTWARE SUPPORT 000110 VINCENZO LUCCHESSI SPIFFY COMPUTER SERVICE DIV. 000120 SEAN O'CONNELL SPIFFY COMPUTER SERVICE DIV. 000130 DELORES QUINTANA INFORMATION CENTER 000140 HEATHER NICHOLLS INFORMATION CENTER 000150 BRUCE ADAMSON MANUFACTURING SYSTEMS 000160 ELIZABETH PIANKA MANUFACTURING SYSTEMS 000170 MASATOSHI YOSHIMURA MANUFACTURING SYSTEMS 000180 MARILYN SCOUTTEN MANUFACTURING SYSTEMS 000190 JAMES WALKER MANUFACTURING SYSTEMS 000200 DAVID BROWN MANUFACTURING SYSTEMS 000210 WILLIAM JONES MANUFACTURING SYSTEMS 000220 JENNIFER LUTZ MANUFACTURING SYSTEMS 000230 JAMES JEFFERSON ADMINISTRATION SYSTEMS 000240 SALVATORE MARINO ADMINISTRATION SYSTEMS 000250 DANIEL SMITH ADMINISTRATION SYSTEMS 000260 SYBIL JOHNSON ADMINISTRATION SYSTEMS 000270 MARIA PEREZ ADMINISTRATION SYSTEMS 000280 ETHEL SCHNEIDER OPERATIONS 000290 JOHN PARKER OPERATIONS 000300 PHILIP SMITH OPERATIONS 000310 MAUDE SETRIGHT OPERATIONS 000320 RAMLAL MEHTA SOFTWARE SUPPORT 000330 WING LEE SOFTWARE SUPPORT 000340 JASON GOUNOT SOFTWARE SUPPORT 200010 DIAN HEMMINGER SPIFFY COMPUTER SERVICE DIV. 200120 GREG ORLANDO SPIFFY COMPUTER SERVICE DIV. 200140 KIM NATZ INFORMATION CENTER 200170 KIYOSHI YAMAMOTO MANUFACTURING SYSTEMS 200220 REBA JOHN MANUFACTURING SYSTEMS 200240 ROBERT MONTEVERDE ADMINISTRATION SYSTEMS 200280 EILEEN SCHWARTZ OPERATIONS 200310 MICHELLE SPRINGER OPERATIONS 200330 HELENA WONG SOFTWARE SUPPORT 200340 ROY ALONZO SOFTWARE SUPPORT - - - BRANCH OFFICE H2 - - - BRANCH OFFICE I2 - - - BRANCH OFFICE G2 - - - DEVELOPMENT CENTER - - - BRANCH OFFICE F2 - - - BRANCH OFFICE J2
db2 => SELECT empno, firstnme, lastname, deptnamedb2 (cont.) => FROM employee FULL OUTER JOIN departmentdb2 (cont.) => ON workdept = deptnodb2 (cont.) => ; EMPNO FIRSTNME LASTNAME DEPTNAME ------ ------------ --------------- ------------------------------------000010 CHRISTINE HAAS SPIFFY COMPUTER SERVICE DIV. 000020 MICHAEL THOMPSON PLANNING 000030 SALLY KWAN INFORMATION CENTER 000050 JOHN GEYER SUPPORT SERVICES 000060 IRVING STERN MANUFACTURING SYSTEMS 000070 EVA PULASKI ADMINISTRATION SYSTEMS 000090 EILEEN HENDERSON OPERATIONS 000100 THEODORE SPENSER SOFTWARE SUPPORT 000110 VINCENZO LUCCHESSI SPIFFY COMPUTER SERVICE DIV. 000120 SEAN O'CONNELL SPIFFY COMPUTER SERVICE DIV. 000130 DELORES QUINTANA INFORMATION CENTER 000140 HEATHER NICHOLLS INFORMATION CENTER 000150 BRUCE ADAMSON MANUFACTURING SYSTEMS 000160 ELIZABETH PIANKA MANUFACTURING SYSTEMS 000170 MASATOSHI YOSHIMURA MANUFACTURING SYSTEMS 000180 MARILYN SCOUTTEN MANUFACTURING SYSTEMS 000190 JAMES WALKER MANUFACTURING SYSTEMS 000200 DAVID BROWN MANUFACTURING SYSTEMS 000210 WILLIAM JONES MANUFACTURING SYSTEMS 000220 JENNIFER LUTZ MANUFACTURING SYSTEMS 000230 JAMES JEFFERSON ADMINISTRATION SYSTEMS 000240 SALVATORE MARINO ADMINISTRATION SYSTEMS 000250 DANIEL SMITH ADMINISTRATION SYSTEMS 000260 SYBIL JOHNSON ADMINISTRATION SYSTEMS 000270 MARIA PEREZ ADMINISTRATION SYSTEMS 000280 ETHEL SCHNEIDER OPERATIONS 000290 JOHN PARKER OPERATIONS 000300 PHILIP SMITH OPERATIONS 000310 MAUDE SETRIGHT OPERATIONS 000320 RAMLAL MEHTA SOFTWARE SUPPORT 000330 WING LEE SOFTWARE SUPPORT 000340 JASON GOUNOT SOFTWARE SUPPORT 200010 DIAN HEMMINGER SPIFFY COMPUTER SERVICE DIV. 200120 GREG ORLANDO SPIFFY COMPUTER SERVICE DIV. 200140 KIM NATZ INFORMATION CENTER 200170 KIYOSHI YAMAMOTO MANUFACTURING SYSTEMS 200220 REBA JOHN MANUFACTURING SYSTEMS 200240 ROBERT MONTEVERDE ADMINISTRATION SYSTEMS 200280 EILEEN SCHWARTZ OPERATIONS 200310 MICHELLE SPRINGER OPERATIONS 200330 HELENA WONG SOFTWARE SUPPORT 200340 ROY ALONZO SOFTWARE SUPPORT - - - BRANCH OFFICE H2 - - - BRANCH OFFICE I2 - - - BRANCH OFFICE G2 - - - DEVELOPMENT CENTER - - - BRANCH OFFICE F2 - - - BRANCH OFFICE J2 99999 killkill Huang -
- 终于找到一个有助理解left/right/full outer join的例子
- 终于找到一个有助理解left/right/full outer join的例子
- Sql语句中的inner join ,left outer join ,right outer join ,full join 的理解
- SQL中 inner join、 left outer join 、right outer join、 full outer join之间的区别
- [nature][ left / right / full outer] join 连接的区别
- 【SQL】inner join、outer join、cross join和left join、right join、full join的区别
- 关于sql的full outer join,left join,right join,inner join, cross join 的说明
- SQL的JOIN语法解析(inner join, left join, right join, full outer join的区别)
- SQL的JOIN语法解析(inner join, left join, right join, full outer join的区别)
- left join , right join , full outer join , inner join
- cross join & natural join & inner join & left outer join & right outer join & full outer join
- SQL的inner join、left join、right join、full outer join、union、union all的区别
- SQL的inner join、left join、right join、full outer join、union、union all的区别
- SQL中的join的详细用法！inner join,full outer join,left join,right jion
- SQL中的join的详细用法！inner join,full outer join,left join,right jion
- SQL中的join的详细用法！inner join,full outer join,left join,right jion
- inner join、 left join 、right join、full outer join之间的区别
- 图解SQL inner join、left join、right join、full outer join、union、union all的区别
- javascript全等号和非全等号
- STL学习总结之初入容器
- 学习网络安全的网站
- Ubuntu10.10使用软件中心安装mysql5.1
- 重叠I/O模型(缩减版)
- 终于找到一个有助理解left/right/full outer join的例子
- C#小小知识点
- 算法应用-汉诺塔
- jsp 生成 word excel pdf
- 模板函数初步探究1
- Java 调用svnkit实现svn功能
- 黑马程序员__SQL Server常见数据类型比较
- Css Bisics翻译说明
- 使用OTP原理构建一个非阻塞的TCP服务器
