mysql - SQL 使用两个子查询

Question

我正在学习 SQL 子查询。这是我在书中使用的子查询:

SELECT account_id, product_cd, cust_id
FROM account
WHERE open_branch_id = (
    SELECT branch_id
    FROM branch
    WHERE name = 'Woburn Branch'
) AND open_emp_id IN (
    SELECT emp_id
    FROM employee
    WHERE title = 'Teller' OR title = 'Head Teller'
);

结果:

+------------+------------+---------+
| account_id | product_cd | cust_id |
+------------+------------+---------+
|          1 | CHK        |       1 |
|          2 | SAV        |       1 |
|          3 | CD         |       1 |
|          4 | CHK        |       2 |
|          5 | SAV        |       2 |
|         17 | CD         |       7 |
|         27 | BUS        |      11 |
+------------+------------+---------+

我查看了这个查询，试图解释和理解它以及它的子句背后的原因，但是，我不明白最后一个 AND 子句 AND open_emp_id IN 的原因...

我注意到只有

SELECT account_id, product_cd, cust_id
FROM account
WHERE open_branch_id = (
    SELECT branch_id
    FROM branch
    WHERE name = 'Woburn Branch'
)

你会得到与上面相同的结果。谁能向我解释最后一个 AND open_emp_id IN 子句背后的原因以及省略它会如何影响最终结果？

子查询中使用的表:

账户表

+------------+------------+---------+------------+------------+--------------------+--------+----------------+-------------+---------------+-----------------+
| account_id | product_cd | cust_id | open_date  | close_date | last_activity_date | status | open_branch_id | open_emp_id | avail_balance | pending_balance |
+------------+------------+---------+------------+------------+--------------------+--------+----------------+-------------+---------------+-----------------+
|          1 | CHK        |       1 | 2000-01-15 | NULL       | 2005-01-04         | ACTIVE |              2 |          10 |       1057.75 |         1057.75 |
|          2 | SAV        |       1 | 2000-01-15 | NULL       | 2004-12-19         | ACTIVE |              2 |          10 |        500.00 |          500.00 |
|          3 | CD         |       1 | 2004-06-30 | NULL       | 2004-06-30         | ACTIVE |              2 |          10 |       3000.00 |         3000.00 |
|          4 | CHK        |       2 | 2001-03-12 | NULL       | 2004-12-27         | ACTIVE |              2 |          10 |       2258.02 |         2258.02 |
|          5 | SAV        |       2 | 2001-03-12 | NULL       | 2004-12-11         | ACTIVE |              2 |          10 |        200.00 |          200.00 |
|          7 | CHK        |       3 | 2002-11-23 | NULL       | 2004-11-30         | ACTIVE |              3 |          13 |       1057.75 |         1057.75 |
|          8 | MM         |       3 | 2002-12-15 | NULL       | 2004-12-05         | ACTIVE |              3 |          13 |       2212.50 |         2212.50 |
|         10 | CHK        |       4 | 2003-09-12 | NULL       | 2005-01-03         | ACTIVE |              1 |           1 |        534.12 |          534.12 |
|         11 | SAV        |       4 | 2000-01-15 | NULL       | 2004-10-24         | ACTIVE |              1 |           1 |        767.77 |          767.77 |
|         12 | MM         |       4 | 2004-09-30 | NULL       | 2004-11-11         | ACTIVE |              1 |           1 |       5487.09 |         5487.09 |
|         13 | CHK        |       5 | 2004-01-27 | NULL       | 2005-01-05         | ACTIVE |              4 |          16 |       2237.97 |         2897.97 |
|         14 | CHK        |       6 | 2002-08-24 | NULL       | 2004-11-29         | ACTIVE |              1 |           1 |        122.37 |          122.37 |
|         15 | CD         |       6 | 2004-12-28 | NULL       | 2004-12-28         | ACTIVE |              1 |           1 |      10000.00 |        10000.00 |
|         17 | CD         |       7 | 2004-01-12 | NULL       | 2004-01-12         | ACTIVE |              2 |          10 |       5000.00 |         5000.00 |
|         18 | CHK        |       8 | 2001-05-23 | NULL       | 2005-01-03         | ACTIVE |              4 |          16 |       3487.19 |         3487.19 |
|         19 | SAV        |       8 | 2001-05-23 | NULL       | 2004-10-12         | ACTIVE |              4 |          16 |        387.99 |          387.99 |
|         21 | CHK        |       9 | 2003-07-30 | NULL       | 2004-12-15         | ACTIVE |              1 |           1 |        125.67 |          125.67 |
|         22 | MM         |       9 | 2004-10-28 | NULL       | 2004-10-28         | ACTIVE |              1 |           1 |       9345.55 |         9845.55 |
|         23 | CD         |       9 | 2004-06-30 | NULL       | 2004-06-30         | ACTIVE |              1 |           1 |       1500.00 |         1500.00 |
|         24 | CHK        |      10 | 2002-09-30 | NULL       | 2004-12-15         | ACTIVE |              4 |          16 |      23575.12 |        23575.12 |
|         25 | BUS        |      10 | 2002-10-01 | NULL       | 2004-08-28         | ACTIVE |              4 |          16 |          0.00 |            0.00 |
|         27 | BUS        |      11 | 2004-03-22 | NULL       | 2004-11-14         | ACTIVE |              2 |          10 |       9345.55 |         9345.55 |
|         28 | CHK        |      12 | 2003-07-30 | NULL       | 2004-12-15         | ACTIVE |              4 |          16 |      38552.05 |        38552.05 |
|         29 | SBL        |      13 | 2004-02-22 | NULL       | 2004-12-17         | ACTIVE |              3 |          13 |      50000.00 |        50000.00 |
+------------+------------+---------+------------+------------+--------------------+--------+----------------+-------------+---------------+-----------------+

分支表:

+-----------+---------------+----------------------+---------+-------+-------+
| branch_id | name          | address              | city    | state | zip   |
+-----------+---------------+----------------------+---------+-------+-------+
|         1 | Headquarters  | 3882 Main St.        | Waltham | MA    | 02451 |
|         2 | Woburn Branch | 422 Maple St.        | Woburn  | MA    | 01801 |
|         3 | Quincy Branch | 125 Presidential Way | Quincy  | MA    | 02169 |
|         4 | So. NH Branch | 378 Maynard Ln.      | Salem   | NH    | 03079 |
+-----------+---------------+----------------------+---------+-------+-------+

员工表:

+--------+----------+-----------+------------+----------+-----------------+---------+--------------------+--------------------+
| 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 |
+--------+----------+-----------+------------+----------+-----------------+---------+--------------------+--------------------+

Answer 1

AND open_emp_id IN 子句仅选择职位为“Teller”或“Head Teller”的员工 ID。在您的情况下，结果没有什么不同，因为您正在寻找名称为“Woburn Branch”的分支机构，而那里的所有员工都只有“出纳员”或“总出纳员”的头衔，这只是一个巧合。

|     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 |

在第一个查询中将分支名称更改为“总部”，您将看到子查询产生的差异。 “总部”中没有“出纳员”或“总出纳员”头衔的任何员工都将被排除在外。