- 浏览: 41539 次
- 性别:
- 来自: 大连
文章分类
最新评论
终于找到一个有助理解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之间的区别
* LEFT OUTER JOIN:一个表满足条件的行,和另一个表的所有行。 * RIGHT OUTER JOIN:与 LEFT 相同,但两个表的角色互换。 * FULL OUTER JOIN:LEFT OUTER 和 RIGHT OUTER 中所有行的超集。 内连接(Inner Join) ...
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/inner join on/full join on/full outer join on/left join on/right join on/cross join on; 在使用jion时,on和where条件的区别;
SQL中大概有这么几种JOIN:cross joininner joinleft outer joinright outer joinfull outer join他们都是基于cross join(笛卡尔乘积),举例说明
这三者的区别很多人都应该不是很清楚,包括我自己,下面我们一起来看看,如果你使用join连表,缺陷的情况下是inner join,另外,开发中使用的left join和right join属于outer join,而outer join还包括full join....
我也是别人问起,才查找资料看了下,跟自己之前的认识差不多,如果你使用join连表,缺陷的情况下是inner join,另外,开发中使用的left join和right join属于outer join,另外outer join还包括full join.下面我通过...
文章目录SQL 连接(JOIN)不同的 SQL JOINSQL INNER JOIN 关键字SQL LEFT JOIN 关键字SQL RIGHT JOIN 关键字SQL FULL OUTER JOIN 关键字 SQL 连接(JOIN) SQL join 用于把来自两个或多个表的行结合起来。 下图展示了 ...
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作为一个中间件,可以提供跨库JOIN的解决方案,满足用户的需求。 1. 跨库JOIN不分库 在不分库的情况下,跨库JOIN可以使用Mysql的JOIN语句来实现。Mysql提供了多种JOIN类型,包括INNER JOIN、LEFT JOIN、RIGHT...
ORACLE的优化器共有3种 ... 在缺省情况下,ORACLE采用CHOOSE优化器, 为了避免那些不必要的全表扫描(full table scan) , 你必须尽量避免使用CHOOSE优化器,而直接采用基于规则或者基于成本的优化器
FROM子句关键字 相应的结果集 ...LEFT OUTER JOIN 一个表满足条件的行,和另一个表的所有行 RIGHT OUTER JOIN 与LEFT相同,但两个表的角色互换 FULL OUTER JOIN LEFT OUTER 和 RIGHT OUTER中所有行的超集
1、左连接 left join 或 left outer join 2、右连接 right join 或 right outer join 3、完全外连接 full join 或 full outer join 二、内连接 join 或 inner join 三、交叉连接 cross join 四、两表关系为一对多...
内连接操作的结果只输出两个...外连接分为左外连接(Left Outer Join)和右外连接(Right Outer Join)和完全外连接(Full Outer Join)。SQL标准规定外连接必须使用OUTER JOIN关键字,不存在类似于旧式内连接的语法。
hive练习数据和hive练习题包含了hive练习数据,hive数据的建表ddl和hive练习题,非常...LEFT,RIGHT 和 FULL OUTER JOIN LEFT SEMI JOIN Hive当前没有实现 IN/EXISTS 子查询,可以用 LEFT SEMI JOIN 重写子查询语句。
Microsoft SQL Server 2000 支持 SQL-92 关键字,包括 LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN 等。这些关键字可以在 FROM 子句中使用,以指定外联接的类型。 旧式 Transact-SQL 外联接语法 SQL ...
在 FROM 子句中指定外联接时,可以由下列几组关键字中的一组指定: LEFT JOIN 或 LEFT OUTER JOIN。 左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行,而不仅仅 是联接列所匹配的行。如果左表的某行在...
[LEFT | RIGHT | FULL OUTER JOIN 表名称2]; 1. 交叉连接:CROSS JOIN,主要功能是产生笛卡儿积,简单实现多表查询; SELECT * FROM emp CROSS JOIN dept; 2. 自然连接:NATURAL JOIN,自动使用关联字段消除...
在 FROM 子句中指定外联接时,可以由下列几组关键字中的一组指定: LEFT JOIN 或 LEFT OUTER JOIN。 左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行,而不仅仅是联接列所匹配的行。如果...
缺省情况下是inner join,开发中使用的left join和right join属于outer join,另外outer join还包括full join.下面我通过图标让大家认识它们的区别。 现有两张表,Table A 是左边的表。Table B 是右边的表。其各有四条...