近日在学习《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 的结果：
LEFT OUT JOIN
db2 => SELECT empno, firstnme, lastname, deptname
db2 (cont.) => FROM employee LEFT OUTER JOIN department
db2 (cont.) => ON workdept = deptno
db2 (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 - RIGHT OUTER JOIN
db2 => SELECT empno, firstnme, lastname, deptname
db2 (cont.) => FROM employee RIGHT OUTER JOIN department
db2 (cont.) => ON workdept = deptno
db2 (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 FULL OUTER JOIN
db2 => SELECT empno, firstnme, lastname, deptname
db2 (cont.) => FROM employee FULL OUTER JOIN department
db2 (cont.) => ON workdept = deptno
db2 (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 - 
