OCP-1Z0-051 第124题 JOIN内连接

一、原题

View the Exhibit and examine the structure of the PROMOTIONS, SALES, and CUSTOMER tables.

You need to generate a report showing the promo name along with the customer name for all products that were sold during their promo campaign and before 30th October 2007.
You issue the following query:
SQL> SELECT promo_name,cust_name
              FROM promotions p JOIN sales s
                   ON(time_id BETWEEN promo_begin_date AND promo_end_date)
                JOIN customer c
                  ON (s.cust_id = c.cust_id)
                AND time_id < '30-oct-2007';
Which statement is true regarding the above query?
A. It executes successfully and gives the required result.
B. It executes successfully but does not give the required result.
C. It produces an error because the join order of the tables is incorrect.
D. It produces an error because equijoin and nonequijoin conditions cannot be used in the same SELECT statement.

答案：B

二、题目翻译

查看PROMOTIONS、SALES和 CUSTOMER表的结构:
要生成一个报表，显示所有产品的promo name和customer name，销售的产品是在促销活动期间，并且在2007年10月30日以前。
执行下面的查询
关于上面的查询哪句话是正确的？
A.执行成功，并给出正确结果。
B.执行成功，但不能给出正确结果。
C.报错，因为表的连接顺序不正确。
D.报错，因为等值连接与非等值连接不能用在同一个SELECT语句中。

三、题目解析

这个sql虽然执行成功，但是结果不正确， promotions和sales表之间应该还有一个关联条件是promo_id相等。

四、测试

SQL> create table promotions(
  2  PROMO_ID NUMBER(2) NOT NULL,
  3  promo_name varchar2(10),
  4  promo_cat varchar2(10),
  5  promo_cost number(8,2),
  6  promo_begin_date date,
  7  promo_end_date date
  8  );

Table created.

SQL> create table sales(
  2  prod_id number(3) not null,
  3  promo_id number(3) not null,
  4  time_id date,
  5  qty_sold number(6,2),
  6  cust_id number(2) not null
  7  );

Table created.

SQL> create table customer(
  2  cust_id number(3) not null,
  3  cust_name varchar2(20),
  4  cust_address varchar(30)
  5  );

Table created.

SQL> insert into promotions values(1,'First','action1',500,to_date('2007-1-5','yyyy-mm-dd'),to_date('2007-1-30','yyyy-mm-dd'));

1 row created.

SQL> insert into promotions values(2,'Second','action2',800,to_date('2007-10-25','yyyy-mm-dd'),to_date('2007-11-5','yyyy-mm-dd'));

1 row created.

SQL> commit;

Commit complete.

SQL> insert into sales values(101,1,to_date('2007-1-10','yyyy-mm-dd'),2,21);

1 row created.

SQL> insert into sales values(102,2,to_date('2007-10-28','yyyy-mm-dd'),5,22);

1 row created.

SQL> insert into sales values(103,2,to_date('2007-11-2','yyyy-mm-dd'),3,23);

1 row created.

SQL> insert into sales values(104,3,to_date('2007-11-8','yyyy-mm-dd'),6,23);

1 row created.

SQL> insert into sales values(105,3,to_date('2007-10-28','yyyy-mm-dd'),4,21);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into customer values(21,'cust1','loc1');

1 row created.

SQL> insert into customer values(22,'cust2','loc2');

1 row created.

SQL> insert into customer values(23,'cust3','loc3');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from promotions;

  PROMO_ID PROMO_NAME           PROMO_CAT            PROMO_COST PROMO_BEGIN_ PROMO_END_DA
---------- -------------------- -------------------- ---------- ------------ ------------
         1 First                action1                     500 05-JAN-07    30-JAN-07
         2 Second               action2                     800 25-OCT-07    05-NOV-07
         3 Third                action3                     800 10-MAY-07    25-MAY-07

SQL> select * FROM SALES;

   PROD_ID   PROMO_ID TIME_ID        QTY_SOLD    CUST_ID
---------- ---------- ------------ ---------- ----------
       101          1 10-JAN-07             2         21
       102          2 28-OCT-07             5         22
       103          2 02-NOV-07             3         23
       104          3 08-NOV-07             6         23
       105          3 28-OCT-07             4         21

SQL> select * from customer;

   CUST_ID CUST_NAME                                CUST_ADDRESS
---------- ---------------------------------------- ------------------------------------------
        21 cust1                                    loc1
        22 cust2                                    loc2
        23 cust3                                    loc3

SQL>  SELECT promo_name,cust_name
  2  FROM promotions p JOIN sales s
  3  ON(time_id BETWEEN promo_begin_date AND promo_end_date)
  4  JOIN customer c
  5  ON (s.cust_id = c.cust_id) AND time_id < '30-oct-2007';

PROMO_NAME           CUST_NAME
-------------------- ----------------------------------------
Second               cust1
First                cust1
Second               cust2

       很明显，这里Second cust1不是在活动期间的，这条，其实就是sales表中的是最后一条记录，它的promo_id是3，但日期却不在这个区间之间，而是在promo_id=2的区间内。
105          3 28-OCT-07             4         21

改成下面这样，才正确了：

SQL> SELECT promo_name,cust_name
  2  FROM promotions p JOIN sales s
  3  ON((time_id BETWEEN promo_begin_date AND promo_end_date) and s.promo_id=p.promo_id)
  4  JOIN customer c
  5  ON (s.cust_id = c.cust_id) AND time_id < '30-oct-2007';

PROMO_NAME           CUST_NAME
-------------------- ----------------------------------------
First                cust1
Second               cust2

      表连接的用法，详见：
             http://blog.youkuaiyun.com/holly2008/article/details/25704471