近日在学习《Understading DB2》的时候找到了一个例子,对于理解 left/right/full 三种 outer join 的大有裨益。
先看样本数据,来自DB2的示例数据库 sample:
01 | db2 => insert into employee values ( '99999' , 'killkill' , 'N' , 'Huang' , null , null , null , 'nothing' ,16, 'M' , null , null , null , null ) ; |
02 | DB20000I The SQL command completed successfully. |
04 | db2 => SELECT empno, firstnme, lastname, workdept | db2 => SELECT deptno, deptname from department order by 1; |
05 | db2 (cont.) => FROM employee order by 4; | |
07 | EMPNO FIRSTNME LASTNAME WORKDEPT | DEPTNO DEPTNAME |
09 | 000010 CHRISTINE HAAS A00 | A00 SPIFFY COMPUTER SERVICE DIV. |
10 | 000110 VINCENZO LUCCHESSI A00 | B01 PLANNING |
11 | 000120 SEAN O'CONNELL A00 | C01 INFORMATION CENTER |
12 | 200010 DIAN HEMMINGER A00 | D01 DEVELOPMENT CENTER |
13 | 200120 GREG ORLANDO A00 | D11 MANUFACTURING SYSTEMS |
14 | 000020 MICHAEL THOMPSON B01 | D21 ADMINISTRATION SYSTEMS |
15 | 000030 SALLY KWAN C01 | E01 SUPPORT SERVICES |
16 | 000130 DELORES QUINTANA C01 | E11 OPERATIONS |
17 | 000140 HEATHER NICHOLLS C01 | E21 SOFTWARE SUPPORT |
18 | 200140 KIM NATZ C01 | F22 BRANCH OFFICE F2 |
19 | 000060 IRVING STERN D11 | G22 BRANCH OFFICE G2 |
20 | 000150 BRUCE ADAMSON D11 | H22 BRANCH OFFICE H2 |
21 | 000160 ELIZABETH PIANKA D11 | I22 BRANCH OFFICE I2 |
22 | 000170 MASATOSHI YOSHIMURA D11 | J22 BRANCH OFFICE J2 |
23 | 000180 MARILYN SCOUTTEN D11 | |
24 | 000190 JAMES WALKER D11 | 14 record(s) selected. |
25 | 000200 DAVID BROWN D11 | |
26 | 000210 WILLIAM JONES D11 | |
27 | 000220 JENNIFER LUTZ D11 | |
28 | 200170 KIYOSHI YAMAMOTO D11 | |
30 | 000070 EVA PULASKI D21 | |
31 | 000230 JAMES JEFFERSON D21 | |
32 | 000240 SALVATORE MARINO D21 | |
33 | 000250 DANIEL SMITH D21 | |
34 | 000260 SYBIL JOHNSON D21 | |
35 | 000270 MARIA PEREZ D21 | |
36 | 200240 ROBERT MONTEVERDE D21 | |
37 | 000050 JOHN GEYER E01 | |
38 | 000090 EILEEN HENDERSON E11 | |
39 | 000280 ETHEL SCHNEIDER E11 | |
40 | 000290 JOHN PARKER E11 | |
41 | 000300 PHILIP SMITH E11 | |
42 | 000310 MAUDE SETRIGHT E11 | |
43 | 200280 EILEEN SCHWARTZ E11 | |
44 | 200310 MICHELLE SPRINGER E11 | |
45 | 000100 THEODORE SPENSER E21 | |
46 | 000320 RAMLAL MEHTA E21 | |
48 | 000340 JASON GOUNOT E21 | |
49 | 200330 HELENA WONG E21 | |
50 | 200340 ROY ALONZO E21 | |
51 | 99999 killkill Huang - | |
注意,我故意插了一条没有对应部门的测试数据到 employee 表中,现在看看 left/right/full out join 的结果:
LEFT OUT JOIN
01 | db2 => SELECT empno, firstnme, lastname, deptname |
02 | db2 (cont.) => FROM employee LEFT OUTER JOIN department |
03 | db2 (cont.) => ON workdept = deptno |
06 | EMPNO FIRSTNME LASTNAME DEPTNAME |
08 | 000010 CHRISTINE HAAS SPIFFY COMPUTER SERVICE DIV. |
09 | 000020 MICHAEL THOMPSON PLANNING |
10 | 000030 SALLY KWAN INFORMATION CENTER |
11 | 000050 JOHN GEYER SUPPORT SERVICES |
12 | 000060 IRVING STERN MANUFACTURING SYSTEMS |
13 | 000070 EVA PULASKI ADMINISTRATION SYSTEMS |
14 | 000090 EILEEN HENDERSON OPERATIONS |
15 | 000100 THEODORE SPENSER SOFTWARE SUPPORT |
16 | 000110 VINCENZO LUCCHESSI SPIFFY COMPUTER SERVICE DIV. |
17 | 000120 SEAN O'CONNELL SPIFFY COMPUTER SERVICE DIV. |
18 | 000130 DELORES QUINTANA INFORMATION CENTER |
19 | 000140 HEATHER NICHOLLS INFORMATION CENTER |
20 | 000150 BRUCE ADAMSON MANUFACTURING SYSTEMS |
21 | 000160 ELIZABETH PIANKA MANUFACTURING SYSTEMS |
22 | 000170 MASATOSHI YOSHIMURA MANUFACTURING SYSTEMS |
23 | 000180 MARILYN SCOUTTEN MANUFACTURING SYSTEMS |
24 | 000190 JAMES WALKER MANUFACTURING SYSTEMS |
25 | 000200 DAVID BROWN MANUFACTURING SYSTEMS |
26 | 000210 WILLIAM JONES MANUFACTURING SYSTEMS |
27 | 000220 JENNIFER LUTZ MANUFACTURING SYSTEMS |
28 | 000230 JAMES JEFFERSON ADMINISTRATION SYSTEMS |
29 | 000240 SALVATORE MARINO ADMINISTRATION SYSTEMS |
30 | 000250 DANIEL SMITH ADMINISTRATION SYSTEMS |
31 | 000260 SYBIL JOHNSON ADMINISTRATION SYSTEMS |
32 | 000270 MARIA PEREZ ADMINISTRATION SYSTEMS |
33 | 000280 ETHEL SCHNEIDER OPERATIONS |
34 | 000290 JOHN PARKER OPERATIONS |
35 | 000300 PHILIP SMITH OPERATIONS |
36 | 000310 MAUDE SETRIGHT OPERATIONS |
37 | 000320 RAMLAL MEHTA SOFTWARE SUPPORT |
38 | 000330 WING LEE SOFTWARE SUPPORT |
39 | 000340 JASON GOUNOT SOFTWARE SUPPORT |
40 | 200010 DIAN HEMMINGER SPIFFY COMPUTER SERVICE DIV. |
41 | 200120 GREG ORLANDO SPIFFY COMPUTER SERVICE DIV. |
42 | 200140 KIM NATZ INFORMATION CENTER |
43 | 200170 KIYOSHI YAMAMOTO MANUFACTURING SYSTEMS |
44 | 200220 REBA JOHN MANUFACTURING SYSTEMS |
45 | 200240 ROBERT MONTEVERDE ADMINISTRATION SYSTEMS |
46 | 200280 EILEEN SCHWARTZ OPERATIONS |
47 | 200310 MICHELLE SPRINGER OPERATIONS |
48 | 200330 HELENA WONG SOFTWARE SUPPORT |
49 | 200340 ROY ALONZO SOFTWARE SUPPORT |
RIGHT OUTER JOIN
01 | db2 => SELECT empno, firstnme, lastname, deptname |
02 | db2 (cont.) => FROM employee RIGHT OUTER JOIN department |
03 | db2 (cont.) => ON workdept = deptno |
06 | EMPNO FIRSTNME LASTNAME DEPTNAME |
08 | 000010 CHRISTINE HAAS SPIFFY COMPUTER SERVICE DIV. |
09 | 000020 MICHAEL THOMPSON PLANNING |
10 | 000030 SALLY KWAN INFORMATION CENTER |
11 | 000050 JOHN GEYER SUPPORT SERVICES |
12 | 000060 IRVING STERN MANUFACTURING SYSTEMS |
13 | 000070 EVA PULASKI ADMINISTRATION SYSTEMS |
14 | 000090 EILEEN HENDERSON OPERATIONS |
15 | 000100 THEODORE SPENSER SOFTWARE SUPPORT |
16 | 000110 VINCENZO LUCCHESSI SPIFFY COMPUTER SERVICE DIV. |
17 | 000120 SEAN O'CONNELL SPIFFY COMPUTER SERVICE DIV. |
18 | 000130 DELORES QUINTANA INFORMATION CENTER |
19 | 000140 HEATHER NICHOLLS INFORMATION CENTER |
20 | 000150 BRUCE ADAMSON MANUFACTURING SYSTEMS |
21 | 000160 ELIZABETH PIANKA MANUFACTURING SYSTEMS |
22 | 000170 MASATOSHI YOSHIMURA MANUFACTURING SYSTEMS |
23 | 000180 MARILYN SCOUTTEN MANUFACTURING SYSTEMS |
24 | 000190 JAMES WALKER MANUFACTURING SYSTEMS |
25 | 000200 DAVID BROWN MANUFACTURING SYSTEMS |
26 | 000210 WILLIAM JONES MANUFACTURING SYSTEMS |
27 | 000220 JENNIFER LUTZ MANUFACTURING SYSTEMS |
28 | 000230 JAMES JEFFERSON ADMINISTRATION SYSTEMS |
29 | 000240 SALVATORE MARINO ADMINISTRATION SYSTEMS |
30 | 000250 DANIEL SMITH ADMINISTRATION SYSTEMS |
31 | 000260 SYBIL JOHNSON ADMINISTRATION SYSTEMS |
32 | 000270 MARIA PEREZ ADMINISTRATION SYSTEMS |
33 | 000280 ETHEL SCHNEIDER OPERATIONS |
34 | 000290 JOHN PARKER OPERATIONS |
35 | 000300 PHILIP SMITH OPERATIONS |
36 | 000310 MAUDE SETRIGHT OPERATIONS |
37 | 000320 RAMLAL MEHTA SOFTWARE SUPPORT |
38 | 000330 WING LEE SOFTWARE SUPPORT |
39 | 000340 JASON GOUNOT SOFTWARE SUPPORT |
40 | 200010 DIAN HEMMINGER SPIFFY COMPUTER SERVICE DIV. |
41 | 200120 GREG ORLANDO SPIFFY COMPUTER SERVICE DIV. |
42 | 200140 KIM NATZ INFORMATION CENTER |
43 | 200170 KIYOSHI YAMAMOTO MANUFACTURING SYSTEMS |
44 | 200220 REBA JOHN MANUFACTURING SYSTEMS |
45 | 200240 ROBERT MONTEVERDE ADMINISTRATION SYSTEMS |
46 | 200280 EILEEN SCHWARTZ OPERATIONS |
47 | 200310 MICHELLE SPRINGER OPERATIONS |
48 | 200330 HELENA WONG SOFTWARE SUPPORT |
49 | 200340 ROY ALONZO SOFTWARE SUPPORT |
53 | - - - DEVELOPMENT CENTER |
FULL OUTER JOIN
01 | db2 => SELECT empno, firstnme, lastname, deptname |
02 | db2 (cont.) => FROM employee FULL OUTER JOIN department |
03 | db2 (cont.) => ON workdept = deptno |
06 | EMPNO FIRSTNME LASTNAME DEPTNAME |
08 | 000010 CHRISTINE HAAS SPIFFY COMPUTER SERVICE DIV. |
09 | 000020 MICHAEL THOMPSON PLANNING |
10 | 000030 SALLY KWAN INFORMATION CENTER |
11 | 000050 JOHN GEYER SUPPORT SERVICES |
12 | 000060 IRVING STERN MANUFACTURING SYSTEMS |
13 | 000070 EVA PULASKI ADMINISTRATION SYSTEMS |
14 | 000090 EILEEN HENDERSON OPERATIONS |
15 | 000100 THEODORE SPENSER SOFTWARE SUPPORT |
16 | 000110 VINCENZO LUCCHESSI SPIFFY COMPUTER SERVICE DIV. |
17 | 000120 SEAN O'CONNELL SPIFFY COMPUTER SERVICE DIV. |
18 | 000130 DELORES QUINTANA INFORMATION CENTER |
19 | 000140 HEATHER NICHOLLS INFORMATION CENTER |
20 | 000150 BRUCE ADAMSON MANUFACTURING SYSTEMS |
21 | 000160 ELIZABETH PIANKA MANUFACTURING SYSTEMS |
22 | 000170 MASATOSHI YOSHIMURA MANUFACTURING SYSTEMS |
23 | 000180 MARILYN SCOUTTEN MANUFACTURING SYSTEMS |
24 | 000190 JAMES WALKER MANUFACTURING SYSTEMS |
25 | 000200 DAVID BROWN MANUFACTURING SYSTEMS |
26 | 000210 WILLIAM JONES MANUFACTURING SYSTEMS |
27 | 000220 JENNIFER LUTZ MANUFACTURING SYSTEMS |
28 | 000230 JAMES JEFFERSON ADMINISTRATION SYSTEMS |
29 | 000240 SALVATORE MARINO ADMINISTRATION SYSTEMS |
30 | 000250 DANIEL SMITH ADMINISTRATION SYSTEMS |
31 | 000260 SYBIL JOHNSON ADMINISTRATION SYSTEMS |
32 | 000270 MARIA PEREZ ADMINISTRATION SYSTEMS |
33 | 000280 ETHEL SCHNEIDER OPERATIONS |
34 | 000290 JOHN PARKER OPERATIONS |
35 | 000300 PHILIP SMITH OPERATIONS |
36 | 000310 MAUDE SETRIGHT OPERATIONS |
37 | 000320 RAMLAL MEHTA SOFTWARE SUPPORT |
38 | 000330 WING LEE SOFTWARE SUPPORT |
39 | 000340 JASON GOUNOT SOFTWARE SUPPORT |
40 | 200010 DIAN HEMMINGER SPIFFY COMPUTER SERVICE DIV. |
41 | 200120 GREG ORLANDO SPIFFY COMPUTER SERVICE DIV. |
42 | 200140 KIM NATZ INFORMATION CENTER |
43 | 200170 KIYOSHI YAMAMOTO MANUFACTURING SYSTEMS |
44 | 200220 REBA JOHN MANUFACTURING SYSTEMS |
45 | 200240 ROBERT MONTEVERDE ADMINISTRATION SYSTEMS |
46 | 200280 EILEEN SCHWARTZ OPERATIONS |
47 | 200310 MICHELLE SPRINGER OPERATIONS |
48 | 200330 HELENA WONG SOFTWARE SUPPORT |
49 | 200340 ROY ALONZO SOFTWARE SUPPORT |
53 | - - - DEVELOPMENT CENTER |