Oracle官网的关于join如何使用的小测试（Quiz on Joins）

Oracle数据库的关于join如何使用的小测试

贴下我的测试结果

原题链接：点击访问🔗

题目：

有两个表，plch_departments 和 plch_employees ，共有一个相同字段 department_id ；
现在要将两个表格中三个字段一起查询输出。

题目中提供的方法是 ：
通过 WHERE 条件语句进行比较两表中的department_id，相同时则输出对应的字段。

CREATE TABLE plch_departments (
  department_id INTEGER PRIMARY KEY
 ,department_name VARCHAR2 (100))
/

BEGIN
   INSERT INTO plch_departments
        VALUES (100, 'Marketing');

   INSERT INTO plch_departments
        VALUES (200, 'Catering');

   COMMIT;
END;
/

CREATE TABLE plch_employees
(
   employee_id     INTEGER
 , last_name       VARCHAR2 (100)
 , salary          NUMBER
 , department_id   INTEGER    REFERENCES plch_departments (department_id)
)

/

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

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

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

   COMMIT;
END;
/
Which of the choices contain a query that returns the same dataset as the following query:

SELECT e.last_name, e.salary, d.department_name
  FROM plch_employees e, plch_departments d
 WHERE e.department_id = d.department_id

以下是选项：

---

Choice 1 √

SELECT e.last_name, e.salary, d.department_name
  FROM plch_employees e NATURAL JOIN plch_departments d

• Use the Natural Join syntax to join two tables based on column names they have in common, in this case department_id.

• 即 NATURAL JOIN 时两表必须有一样的列（这里是 department_id ）

---

Choice 2 √

SELECT e.last_name, e.salary, d.department_name
  FROM plch_employees e JOIN plch_departments d USING (department_id)

• JOIN…USING is much like the Natural Join, but in this case you specify the columns to use in the join.

• USING 后的字段即公共字段

---

Choice 3 ×

选错了，虽然和选项2很类似，但 USING 后的字段必须用括号括起来，我以为有不有都可以。

---

Choice 4 √

SELECT e.last_name, e.salary, d.department_name
  FROM    plch_employees e
       JOIN
          plch_departments d
       ON (e.department_id = d.department_id)

• The JOIN…ON syntax is used to join table on the fields you specify. They need not be of the same name and as long as there can be an implicit typecast, they don’t even have to be of the same type.
• JOIN....ON字段名和字段类型不必相同

---

Choice 5 ×

SELECT e.last_name, e.salary, d.department_name
  FROM plch_employees e CROSS JOIN plch_departments d

• A Cross Join joins every record in the first table with every record in the second table therefore resulting in a Cartesian product, which does not give us the result we wanted for this quiz.

• Of course, there may come a time when you actually do need a Cartesian product. In this case, the ANSI syntax is more explicit and manageable than the ‘old’ Oracle syntax - with which Cartesian products are the result of leaving off predicates in the WHERE clause.

• CROSS JOIN 将第一个表中的每个记录与第二个表中的每个记录联接在一起，产生笛卡尔积。

这里不知道笛卡尔积，可以看下图：

---

Choice 6 √

这里也选错了，看到cross join 就没选了。
其实这里的字段department_id值对应是一对一的，一个 100 200，一个 100 200 300，where条件语句，确保了一对一，cross join没有啥影响。

SELECT e.last_name, e.salary, d.department_name
  FROM plch_employees e CROSS JOIN plch_departments d
 WHERE e.department_id = d.department_id

• A Cross Join joins every record in the first table with every record in the second table. The where clause then limits the result of the query. This choice looks an awful lot like the ‘old’ Oracle syntax, just a bit more verbose.

• De advised though that creating a cartesian product and then filtering it may not be very performant and may put a big stress on the available memory.

• 这里也说了下，比较冗余的查询语句，造成内存负担。

---

扩展阅读

参考 《Oracle9i表连接》

感觉和MySQL的差别就在于 NATURAL JOIN、 USING、CROSS JOIN这几个，还好有MySQL的基础，通过上面的选项进行理解起来倒是不难。