`
kb5706
  • 浏览: 41530 次
  • 性别: Icon_minigender_1
  • 来自: 大连
文章分类
社区版块
存档分类
最新评论

终于找到一个有助理解left/right/full outer join的例子

 
阅读更多

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


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



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           -


分享到：
| 事务的概念
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    inner join、 left join 、right join、 outer join之间的区别

    inner join、 left join 、right join、 outer join之间的区别

    SQL中的left outer join,inner join,right outer join用法详解

    * LEFT OUTER JOIN：一个表满足条件的行，和另一个表的所有行。 * RIGHT OUTER JOIN：与 LEFT 相同，但两个表的角色互换。 * FULL OUTER JOIN：LEFT OUTER 和 RIGHT OUTER 中所有行的超集。 内连接（Inner Join） ...

    Which SQL statement is the trump card to the senior software developer

    Within so many kinds of DML statements in SQL, I think four kinds of them, including Case When, Left Outer Join/Left Join / Right Outer Join /Right Join/ Inner Join, WITH AS and UNION/UNION ALL, are ...

    join on 语句及扩展

    Join on/inner join on/full join on/full outer join on/left join on/right join on/cross join on; 在使用jion时，on和where条件的区别;

    sql中join总结

    SQL中大概有这么几种JOIN：cross joininner joinleft outer joinright outer joinfull outer join他们都是基于cross join(笛卡尔乘积)，举例说明

    SQL联合查询inner join、outer join和cross join的区别详解

    这三者的区别很多人都应该不是很清楚，包括我自己，下面我们一起来看看，如果你使用join连表，缺陷的情况下是inner join，另外，开发中使用的left join和right join属于outer join，而outer join还包括full join....

    SQL中inner join、outer join和cross join的区别

    我也是别人问起，才查找资料看了下，跟自己之前的认识差不多，如果你使用join连表，缺陷的情况下是inner join,另外，开发中使用的left join和right join属于outer join,另外outer join还包括full join.下面我通过...

    【SQL高级(三)】【SQL 连接(JOIN)】

    文章目录SQL 连接(JOIN)不同的 SQL JOINSQL INNER JOIN 关键字SQL LEFT JOIN 关键字SQL RIGHT JOIN 关键字SQL FULL OUTER JOIN 关键字 SQL 连接(JOIN) SQL join 用于把来自两个或多个表的行结合起来。 下图展示了 ...

    SQL多表连接查询、多表关联

    sql多表连接查询inner join, left join , right join ,full join ,cross join Posted on 2008-05-07 21:55 我开始懂了 阅读(21431) 评论(9) 编辑 收藏 inner join,full outer join,left join,right jion 内部连接 ...

    Mycat与Mysql跨库JOIN与性能测试

    Mycat作为一个中间件，可以提供跨库JOIN的解决方案，满足用户的需求。 1. 跨库JOIN不分库 在不分库的情况下，跨库JOIN可以使用Mysql的JOIN语句来实现。Mysql提供了多种JOIN类型，包括INNER JOIN、LEFT JOIN、RIGHT...

    oracle性能优化技巧

    ORACLE的优化器共有3种 ... 在缺省情况下，ORACLE采用CHOOSE优化器， 为了避免那些不必要的全表扫描(full table scan) ， 你必须尽量避免使用CHOOSE优化器，而直接采用基于规则或者基于成本的优化器

    sql 外联、内联简单的例子

    FROM子句关键字 相应的结果集 ...LEFT OUTER JOIN 一个表满足条件的行，和另一个表的所有行 RIGHT OUTER JOIN 与LEFT相同，但两个表的角色互换 FULL OUTER JOIN LEFT OUTER 和 RIGHT OUTER中所有行的超集

    SQL语句多表查询

    1、左连接 left join 或 left outer join 2、右连接 right join 或 right outer join 3、完全外连接 full join 或 full outer join 二、内连接 join 或 inner join 三、交叉连接 cross join 四、两表关系为一对多...

    GBASE数据库-外连接和自连接.doc

    内连接操作的结果只输出两个...外连接分为左外连接（Left Outer Join）和右外连接（Right Outer Join）和完全外连接（Full Outer Join）。SQL标准规定外连接必须使用OUTER JOIN关键字，不存在类似于旧式内连接的语法。

    hive练习数据和练习题及答案

    hive练习数据和hive练习题包含了hive练习数据，hive数据的建表ddl和hive练习题，非常...LEFT，RIGHT 和 FULL OUTER JOIN LEFT SEMI JOIN Hive当前没有实现 IN/EXISTS 子查询，可以用 LEFT SEMI JOIN 重写子查询语句。

    非常详细的SQL--JOIN之完全用法

    Microsoft SQL Server 2000 支持 SQL-92 关键字，包括 LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN 等。这些关键字可以在 FROM 子句中使用，以指定外联接的类型。 旧式 Transact-SQL 外联接语法 SQL ...

    MySQL JOIN之完全用法

    在 FROM 子句中指定外联接时，可以由下列几组关键字中的一组指定： LEFT JOIN 或 LEFT OUTER JOIN。 左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅 是联接列所匹配的行。如果左表的某行在...

    视频教程读书笔记之oracle从入门到精通

    [LEFT | RIGHT | FULL OUTER JOIN 表名称2]； 1. 交叉连接：CROSS JOIN，主要功能是产生笛卡儿积，简单实现多表查询； SELECT * FROM emp CROSS JOIN dept； 2. 自然连接：NATURAL JOIN，自动使用关联字段消除...

    非常详细的SQL–JOIN之完全用法

     在 FROM 子句中指定外联接时，可以由下列几组关键字中的一组指定： LEFT JOIN 或 LEFT OUTER JOIN。 左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果...

    Oracle SQL连接查询总结.docx

    缺省情况下是inner join,开发中使用的left join和right join属于outer join,另外outer join还包括full join.下面我通过图标让大家认识它们的区别。 现有两张表，Table A 是左边的表。Table B 是右边的表。其各有四条...

Magicbox
Global site tag (gtag.js) - Google Analytics