近日在学习《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 -