Question

页: 1

 ------------------------------
|           employee           |
 ------------------------------
| employee_id  | employee_name |  
 ------------------------------
|     1        |     one       |
|     2        |     two       |
|     3        |     three     |
 ------------------------------

以及

 -------------------------------------------
|                feedback                   |
 -------------------------------------------
| employee_id (FK)  |       comments        |  
 -------------------------------------------
|         2         |     comment two       |
 -------------------------------------------

遣返所有雇员的最佳(业绩)方式是: 他们是否得到反馈?

我想到的是以下表格,但由于它使用分类,我不清楚这两个表格的记录数量在增加时会如何加快。

SELECT * FROM employee WHERE employee_id NOT IN (SELECT employee_id FROM feedback)

The database is Oracle 以及 all key columns have indexes.

Update

感谢大家,我希望我接受不止一个答案! 这是我在最后使用的(我与其他几张表格一样,我的表结构非常简单。

SELECT 
    e.name, m.name, a.postcode 
FROM 
    employee LEFT OUTER JOIN feedback f on (e.employee_id = f.employee_id),
    address a, manager m 
WHERE a.address_id = e.address_id
AND m.manager_id = e.manager_id
AND f.employee_id IS NULL

Answer 1

You could use:

SELECT e.* 
  FROM employee e 
  LEFT OUTER JOIN feedback f ON (e.employee_id = f.employee_id)
 WHERE f.employee_id IS NULL

这样做应当非常好。我假定ESMPLOYEE-ID栏的索引为......

Try it and see what your explain plan looks like.

EDIT: As you have said you do not have the PLAN table then this article from Tom Kyte (Oracle VP) is useful: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:313616750808

它建议说明为什么每一种解决办法(NOT IN, NOT EXISTS, OUTER JOIN)在当时情况下可能更好。

There is also this from the prolific Don Burleson: http://www.dba-oracle.com/oracle_tips_subq_rewrite.htm

Answer 2

Try to use different queries and compare theirs plans. one more possibility to implement youe query:

select EMPLOYEE_ID from employee
MINUS
select EMPLOYEE_ID from FEEDBACK

Answer 3

The best way to be certain is to try each method with the relevant data - I suggest using not exists:

select * from EMPLOYEE e 
where not exists
(select null from FEEDBACK f where e.EMPLOYEE_ID = f.EMPLOYEE_ID)

Answer 4

明显:

SELECT e.* FROM EMPLOYEE e
LEFT OUTER JOIN FEEDBACK f ON e.EMPLOYEE_ID = f.EMPLOYEE_ID
WHERE f.EMPLOYEE_ID IS NULL

But you can read THIS article about it!

友情链接