时间戳

首页 > 解决方案 > SQL NOT IN JOIN 等效

mysql - SQL NOT IN JOIN 等效

问题描述

桌子：

+--------+----------+-----------+------------+----------+-----------------+---------+--------------------+--------------------+
| emp_id | fname    | lname     | start_date | end_date | superior_emp_id | dept_id | title              | assigned_branch_id |
+--------+----------+-----------+------------+----------+-----------------+---------+--------------------+--------------------+
|      1 | Michael  | Smith     | 2005-06-22 | NULL     |            NULL |       3 | President          |                  1 |
|      2 | Susan    | Barker    | 2006-09-12 | NULL     |               1 |       3 | Vice President     |                  1 |
|      3 | Robert   | Tyler     | 2005-02-09 | NULL     |               1 |       3 | Treasurer          |                  1 |
|      4 | Susan    | Hawthorne | 2006-04-24 | NULL     |               3 |       1 | Operations Manager |                  1 |
|      5 | John     | Gooding   | 2007-11-14 | NULL     |               4 |       2 | Loan Manager       |                  1 |
|      6 | Helen    | Fleming   | 2008-03-17 | NULL     |               4 |       1 | Head Teller        |                  1 |
|      7 | Chris    | Tucker    | 2008-09-15 | NULL     |               6 |       1 | Teller             |                  1 |
|      8 | Sarah    | Parker    | 2006-12-02 | NULL     |               6 |       1 | Teller             |                  1 |
|      9 | Jane     | Grossman  | 2006-05-03 | NULL     |               6 |       1 | Teller             |                  1 |
|     10 | Paula    | Roberts   | 2006-07-27 | NULL     |               4 |       1 | Head Teller        |                  2 |
|     11 | Thomas   | Ziegler   | 2004-10-23 | NULL     |              10 |       1 | Teller             |                  2 |
|     12 | Samantha | Jameson   | 2007-01-08 | NULL     |              10 |       1 | Teller             |                  2 |
|     13 | John     | Blake     | 2004-05-11 | NULL     |               4 |       1 | Head Teller        |                  3 |
|     14 | Cindy    | Mason     | 2006-08-09 | NULL     |              13 |       1 | Teller             |                  3 |
|     15 | Frank    | Portman   | 2007-04-01 | NULL     |              13 |       1 | Teller             |                  3 |
|     16 | Theresa  | Markham   | 2005-03-15 | NULL     |               4 |       1 | Head Teller        |                  4 |
|     17 | Beth     | Fowler    | 2006-06-29 | NULL     |              16 |       1 | Teller             |                  4 |
|     18 | Rick     | Tulman    | 2006-12-12 | NULL     |              16 |       1 | Teller             |                  4 |
+--------+----------+-----------+------------+----------+-----------------+---------+--------------------+--------------------

使用子查询查询：

-- Select employees that do not manage others
SELECT
    emp_id,
    fname,
    lname,
    title
FROM
    employee
WHERE
    emp_id NOT IN (
        SELECT
            superior_emp_id
        FROM
            employee
        WHERE
            superior_emp_id IS NOT NUll
        );

结果：

+--------+----------+----------+----------------+
| emp_id | fname    | lname    | title          |
+--------+----------+----------+----------------+
|      2 | Susan    | Barker   | Vice President |
|      5 | John     | Gooding  | Loan Manager   |
|      7 | Chris    | Tucker   | Teller         |
|      8 | Sarah    | Parker   | Teller         |
|      9 | Jane     | Grossman | Teller         |
|     11 | Thomas   | Ziegler  | Teller         |
|     12 | Samantha | Jameson  | Teller         |
|     14 | Cindy    | Mason    | Teller         |
|     15 | Frank    | Portman  | Teller         |
|     17 | Beth     | Fowler   | Teller         |
|     18 | Rick     | Tulman   | Teller         |
+--------+----------+----------+----------------+

上面的查询工作正常，但我很好奇如何使用连接来完成相同的结果。

到目前为止，这是我所拥有的，但没有返回相同的结果：

SELECT
    e1.emp_id,
    e1.fname,
    e1.lname,
    e1.title
FROM
    employee e1
JOIN
    employee e2 ON e1.emp_id != e2.superior_emp_id
GROUP BY
    e1.emp_id;

结果：

+--------+----------+-----------+--------------------+
| emp_id | fname    | lname     | title              |
+--------+----------+-----------+--------------------+
|      1 | Michael  | Smith     | President          |
|      2 | Susan    | Barker    | Vice President     |
|      3 | Robert   | Tyler     | Treasurer          |
|      4 | Susan    | Hawthorne | Operations Manager |
|      5 | John     | Gooding   | Loan Manager       |
|      6 | Helen    | Fleming   | Head Teller        |
|      7 | Chris    | Tucker    | Teller             |
|      8 | Sarah    | Parker    | Teller             |
|      9 | Jane     | Grossman  | Teller             |
|     10 | Paula    | Roberts   | Head Teller        |
|     11 | Thomas   | Ziegler   | Teller             |
|     12 | Samantha | Jameson   | Teller             |
|     13 | John     | Blake     | Head Teller        |
|     14 | Cindy    | Mason     | Teller             |
|     15 | Frank    | Portman   | Teller             |
|     16 | Theresa  | Markham   | Head Teller        |
|     17 | Beth     | Fowler    | Teller             |
|     18 | Rick     | Tulman    | Teller             |
+--------+----------+-----------+--------------------+

标签： mysqlsql

解决方案

等效查询使用 aleft join和where：

SELECT e.*
FROM employee e LEFT JOIN
     employee es
     ON es.superior_emp_id = e.id
WHERE es.id IS NULL;

这会在superior_emp_id. 但是，您特别不想要匹配 - 因此 theLEFT JOIN和WHEREwhich 会过滤掉任何匹配项。

这通常比您的版本具有更好的性能。一个可比较的版本将使用NOT EXISTS.

推荐阅读