ITPUB论坛-中国最专业的IT技术社区

 
 注册
热搜:
查看: 4333|回复: 5

[每日一题] PL/SQL Challenge 每日一题:2018-1-9 外连接

[复制链接]
论坛徽章:
486
秀才
日期:2015-09-09 10:33:01秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21
跳转到指定楼层
1#
发表于 2018-1-12 05:58 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
(原发表于 2011-7-29)

最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正),其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。

每两周的优胜者可获得itpub奖励的技术图书一本。

以往旧题索引:
/forum.php?m ... eid&typeid=1808

原始出处:


作者: Kim Berg Hansen

运行环境:SQLPLUS, SERVEROUTPUT已打开
注:本题给出答案时候要求给予简要说明才能得到奖品

我创建了如下的表和数据:

CREATE TABLE plch_employees
(
   employee_id   INTEGER
, last_name     VARCHAR2 (20)
)
/

BEGIN
   INSERT INTO plch_employees
        VALUES (100, 'Jobs');

   INSERT INTO plch_employees
        VALUES (200, 'Ellison');

   INSERT INTO plch_employees
        VALUES (300, 'Gates');

   COMMIT;
END;
/

CREATE TABLE plch_bonus
(
   employee_id   INTEGER
, bonus         NUMBER
)
/

BEGIN
   INSERT INTO plch_bonus
        VALUES (100, 100000);

   INSERT INTO plch_bonus
        VALUES (200, 200000);

   COMMIT;
END;
/

我老板想要一个员工清单以及他们的奖金。没有奖金的员工也要列出。哪些选项产生了这个所需的输出:



LAST_NAME                 BONUS
-------------------- ----------
Jobs                     100000
Ellison                  200000
Gates


(A)
SELECT e.last_name
     , b.bonus
FROM plch_employees e
   , plch_bonus b
WHERE b.employee_id = e.employee_id
ORDER BY e.employee_id;

(B)
SELECT e.last_name
     , ( SELECT b.bonus
         FROM plch_bonus b
         WHERE b.employee_id = e.employee_id
       ) bonus
FROM plch_employees e
ORDER BY e.employee_id;

(C)
SELECT e.last_name
     , b.bonus
FROM plch_employees e
   , plch_bonus b
WHERE b.employee_id(+) = e.employee_id
  AND b.bonus(+) > 0
ORDER BY e.employee_id;

(D)
SELECT e.last_name
     , b.bonus
FROM plch_employees e
LEFT OUTER JOIN plch_bonus b
   ON b.employee_id = e.employee_id
ORDER BY e.employee_id;

(E)
SELECT e.last_name
     , b.bonus
FROM plch_employees e
EXISTS JOIN plch_bonus b
   ON b.employee_id = e.employee_id
ORDER BY e.employee_id;

(F)
SELECT e.last_name
     , b.bonus
FROM plch_employees e
   , plch_bonus b
WHERE b.employee_id(+) = e.employee_id
  AND b.bonus > 0
ORDER BY e.employee_id;

(G)
SELECT e.last_name
     , b.bonus
FROM plch_employees e
   , plch_bonus b
WHERE b.employee_id(+) = e.employee_id
ORDER BY e.employee_id;


(H)
SELECT e.last_name
     , b.bonus
FROM plch_employees e
RIGHT OUTER JOIN plch_bonus b
   ON b.employee_id = e.employee_id
ORDER BY e.employee_id;


(I)
SELECT e.last_name
     , b.bonus
FROM plch_employees e
CROSS JOIN plch_bonus b
WHERE b.employee_id = e.employee_id
ORDER BY e.employee_id;
论坛徽章:
35
2012新春纪念徽章
日期:2012-01-04 11:58:44秀才
日期:2016-04-29 15:03:39秀才
日期:2018-03-01 09:58:15秀才
日期:2018-03-01 09:58:47秀才
日期:2018-03-01 09:58:47秀才
日期:2018-03-01 10:05:18秀才
日期:2018-03-01 10:05:34秀才
日期:2018-03-01 10:05:34秀才
日期:2018-03-01 10:05:34技术图书徽章
日期:2018-03-01 10:05:43
2#
发表于 2018-1-12 08:12 | 只看该作者
正确答案:BCDG
A:错误,限制了两张表必须存在同样的employee_id才会输出。
B:正确,使用了子查询,子查询不会影响到外部的查询,所以没有奖金的员工也会显示。
C:正确,为右连接,员工表里面的员工会全部输出,虽然限制了b.bonus(+)>0,但由于使用
了右连接,奖金为空的员工也会输出。
D:正确,使用了左外连接,员工表里面的员工会全部输出。
E:错误,会报错,没有exists join的语法。
F:错误,虽然使用了右连接,但是限制了b.bonus > 0,所以奖金为空的员工不会显示。
G:正确,使用了右连接,员工表里面的员工会全部输出。
H:错误,使用了右外连接,必须在奖金表里面的员工才会显示。
I:错误,使用了笛卡尔乘积连接,加上了where条件,只会显示有奖金的员工。

使用道具 举报

回复
论坛徽章:
127
ITPUB元老
日期:2007-07-04 17:27:50会员2007贡献徽章
日期:2007-09-26 18:42:10现任管理团队成员
日期:2011-05-07 01:45:08优秀写手
日期:2015-01-09 06:00:14版主7段
日期:2015-07-16 02:10:00
3#
发表于 2018-1-12 16:27 | 只看该作者
答案C有争议, 如果那张员工奖金表, 正好有bonus列的值为0, 那这样结果就不对了, 除非奖金表的bonus列的值一定是大于0的, 由于目前关于这个l没有明确的说明, 因此是有争议的,  我觉得答案C的 b.bonus(+) > 0这个连接条件不要写

使用道具 举报

回复
论坛徽章:
486
秀才
日期:2015-09-09 10:33:01秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21
4#
 楼主| 发表于 2018-1-13 05:19 | 只看该作者
答案BCDG,2楼得奖。

A: 这是一个内连接,仅仅返回哪些有对应b记录存在的e记录。所以 Gates不会被显示。这可以通过在b.employee_id后面增加一个(+)进行修正
B: 这是可行的,用了一个标量子查询来获取一个员工的奖金。因为plch_bonus的每个员工最多只有一条记录,如果有多条的话,那么就需要用外连接了。
在某些情况下这可能会性能不佳,例如,当高效计划是两次全表扫描以及散列外连接的情况下。在另外一些情况下,这可能是用来避免低效连接的好技巧。
你必须选择哪一种更合适那个场景。
C: 这是可行的,用了经典的oracle (+)外连接语法。因为我们在b的两个谓词上都有(+), 这就对了,Gates会被显示。如果我们忽略了第二个(+), 它就无效了,因为 "NULL > 0" 不为真。
D: 这是外连接的ANSI风格语法(LEFT JOIN就足够了,OUTER只是更加明确)

它比经典的Oracle (+)语法更有优势:你可以从多个表进行外连接,你不需要记住在所有谓词上使用(+),连接谓词很清楚地和过滤谓词区分出来。另一方面,同时具有连接和过滤谓词有时候可能更加令人迷惑。不论你觉得ANSI风格是好还是坏,这经常只取决于习惯以及语句的复杂程度。

E:
EXISTS JOIN 不是有效的语法,这会报错 ORA-00933. 它必须是 LEFT JOIN 或者 LEFT OUTER JOIN.

F: 这用了经典的 Oracle (+) 外连接语法。对于员工Gates, 一个全表为NULL的b记录会被“创建”出来。过滤谓词 b.bonus > 0 于是变成了 NULL > 0,这不为true (也不是false)。因此这个“凭空创建”的记录会被过滤掉。为了修正必须在b.bonus后面也加上(+)

G: 这用了经典的 Oracle (+) 外连接语法。对于员工Gates, 一个全表为NULL的b记录会被“创建”出来
H: 这和LEFT OUTER JOIN一样是ANSI风格语法(它也可以被写成RIGHT JOIN)。但是它工作于“另外一个方向”,所以应该写成:

FROM plch_bonus b
RIGHT OUTER JOIN plch_employees e

I: CROSS JOIN 创建了一个3名员工和2条奖金记录的6种组合的笛卡尔积。然后过滤子句仅仅从6个组合中选出2条,其employee_id是相匹配的。实际上这是创建内连接的昂贵做法,而且Gates仍然未被显示。

使用道具 举报

回复
论坛徽章:
486
秀才
日期:2015-09-09 10:33:01秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21
5#
 楼主| 发表于 2018-1-13 05:24 | 只看该作者
bell6248 发表于 2018-1-12 16:27
答案C有争议, 如果那张员工奖金表, 正好有bonus列的值为0, 那这样结果就不对了, 除非奖金表的bonus列的 ...

关于这个答案C, 如果在b表里面有奖金为0的,那么就不会被连接上,好像这些记录不存在似的。这样的话名字还会被输出,bonus一列为空。这也不算是违背需求,0就是没有嘛。

使用道具 举报

回复
认证徽章
论坛徽章:
10
林肯
日期:2013-07-30 18:00:55技术图书徽章
日期:2018-03-01 10:21:49秀才
日期:2018-03-01 10:21:25秀才
日期:2018-03-01 10:05:18秀才
日期:2015-12-14 15:09:382015年新春福章
日期:2015-03-06 11:58:18懒羊羊
日期:2015-03-04 14:52:11马上有对象
日期:2015-01-16 13:26:19优秀写手
日期:2014-10-23 06:00:14秀才
日期:2018-03-01 10:21:49
6#
发表于 2018-1-13 12:13 | 只看该作者
对于d相对于经典的(+)更有优势,我总是有点迷糊,能否举个简单的例子说一下。

来自苹果客户端来自客户端

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 |
  | | |
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表