目录
子查询指的是包含在另一个查询中的查询,子查询可以出现在select语句中的各个部分并且被包含在圆括号内。在from子句内,子查询作用是根据其他查询子句产生临时表。
本例中,针对employee表的子查询返回5个列,而外围的查询获取其中3个列。通过别名e来引用子查询
SELECT e.emp_id,e.fname,e.lname FROM (SELECT emp_id,fname,lname,start_date,title FROM employee) e;
+--------+----------+-----------+
| emp_id | fname | lname |
+--------+----------+-----------+
| 1 | Michael | Smith |
| 2 | Susan | Barker |
| 3 | Robert | Tyler |
| 4 | Susan | Hawthorne |
| 5 | John | Gooding |
| 6 | Helen | Fleming |
| 7 | Chris | Tucker |
| 8 | Sarah | Parker |
| 9 | Jane | Grossman |
| 10 | Paula | Roberts |
| 11 | Thomas | Ziegler |
| 12 | Samantha | Jameson |
| 13 | John | Blake |
| 14 | Cindy | Mason |
| 15 | Frank | Portman |
| 16 | Theresa | Markham |
| 17 | Beth | Fowler |
| 18 | Rick | Tulman |
+--------+----------+-----------+
子查询类型
-
单行单列
标量子查询,返回一个单列单行的表,可以位于常用比较运算符(=,<>,<,>,<=,>=)的任意一边。下面为在不等条件中使用标量子查询。
SELECT account_id, product_cd, cust_id, avail_balance FROM account WHERE open_emp_id <>
-> (SELECT e.emp_id FROM employee e INNER JOIN branch b ON e.assigned_branch_id = b.branch_id
-> WHERE e.title='Head Teller' AND b.city='Woburn');
+------------+------------+---------+---------------+
| account_id | product_cd | cust_id | avail_balance |
+------------+------------+---------+---------------+
| 7 | CHK | 3 | 1057.75 |
| 8 | MM | 3 | 2212.50 |
| 10 | CHK | 4 | 534.12 |
| 11 | SAV | 4 | 767.77 |
| 12 | MM | 4 | 5487.09 |
| 13 | CHK | 5 | 2237.97 |
| 14 | CHK | 6 | 122.37 |
| 15 | CD | 6 | 10000.00 |
| 18 | CHK | 8 | 3487.19 |
| 19 | SAV | 8 | 387.99 |
| 21 | CHK | 9 | 125.67 |
| 22 | MM | 9 | 9345.55 |
| 23 | CD | 9 | 1500.00 |
| 24 | CHK | 10 | 23575.12 |
| 25 | BUS | 10 | 0.00 |
| 28 | CHK | 12 | 38552.05 |
| 29 | SBL | 13 | 50000.00 |
+------------+------------+---------+---------------+
如果在等式条件下使用子查询,而子查询又返回多行结果,那么将会出错。
SELECT account_id, product_cd, cust_id, avail_balance FROM account WHERE open_emp_id <>
-> (SELECT e.emp_id FROM employee e INNERJOIN branch b ON e.assigned_branch_id = b.branch_id
-> WHERE e.title='Teller' AND b.city='Woburn');
ERROR 1242 (21000): Subquery returns more than 1 row
-
多行单列子查询
in和 not in运算符
下面查询利用in运算符在右边的过滤条件中构建子查询以查找哪些雇员是主管。
SELECT emp_id, fname, lname, title FROM employee WHERE emp_id IN (SELECT superior_emp_id FROM employee);
+--------+---------+-----------+--------------------+
| emp_id | fname | lname | title |
+--------+---------+-----------+--------------------+
| 1 | Michael | Smith | President |
| 3 | Robert | Tyler | Treasurer |
| 4 | Susan | Hawthorne | Operations Manager |
| 6 | Helen | Fleming | Head Teller |
| 10 | Paula | Roberts | Head Teller |
| 13 | John | Blake | Head Teller |
| 16 | Theresa | Markham | Head Teller |
+--------+---------+-----------+--------------------+
当使用not in或<>运算符比较值集的时候,必须确保值集中不含null值。因为任何一个将值与null进行比较的企图都将产生未知的结果。
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 |
+--------+----------+----------+----------------+
all运算符
将某个单值与集合中的每个值进行比较。可以与比较运算符(=,<>,<,>,<=,>=)配合使用。如<> all就等价于not in,与所有元素都不相等。下面用all查询可用余额小于Frank Tucker所有账户的账户。
SELECT account_id, cust_id, product_cd, avail_balance FROM account
-> WHERE avail_balance < ALL (SELECT a.avail_balance FROM account a
-> INNER JOIN individual i ON a.cust_id=i.cust_id WHERE i.fname='Frank' AND i.lname='Tucker');
+------------+---------+------------+---------------+
| account_id | cust_id | product_cd | avail_balance |
+------------+---------+------------+---------------+
| 2 | 1 | SAV | 500.00 |
| 5 | 2 | SAV | 200.00 |
| 10 | 4 | CHK | 534.12 |
| 11 | 4 | SAV | 767.77 |
| 14 | 6 | CHK | 122.37 |
| 19 | 8 | SAV | 387.99 |
| 21 | 9 | CHK | 125.67 |
| 25 | 10 | BUS | 0.00 |
+------------+---------+------------+---------------+
any运算符
将一个值与值集中每一个成员比较。但只要有一个比较成功,则条件为真。
SELECT account_id, cust_id, product_cd, avail_balance FROM account WHERE avail_balance > ANY
-> (SELECT a.avail_balance FROM account a INNER JOIN individual i
-> ON a.cust_id=i.cust_id WHERE i.fname='Frank' AND i.lname='Tucker');
+------------+---------+------------+---------------+
| account_id | cust_id | product_cd | avail_balance |
+------------+---------+------------+---------------+
| 3 | 1 | CD | 3000.00 |
| 4 | 2 | CHK | 2258.02 |
| 8 | 3 | MM | 2212.50 |
| 12 | 4 | MM | 5487.09 |
| 13 | 5 | CHK | 2237.97 |
| 15 | 6 | CD | 10000.00 |
| 17 | 7 | CD | 5000.00 |
| 18 | 8 | CHK | 3487.19 |
| 22 | 9 | MM | 9345.55 |
| 23 | 9 | CD | 1500.00 |
| 24 | 10 | CHK | 23575.12 |
| 27 | 11 | BUS | 9345.55 |
| 28 | 12 | CHK | 38552.05 |
| 29 | 13 | SBL | 50000.00 |
+------------+---------+------------+---------------+
-
多列多行子查询
下面查询使用两个子查询检索出Woburn分行的ID以及所有银行柜台员的ID,同时包括包含查询使用这个信息查找所有Woburn分行柜台员开立的账户。
SELECT account_id, product_cd, cust_id FROM account WHERE
-> open_branch_id = (SELECT branch_id FROM branch WHERE name='Woburn Branch') AND 检索出Woburn分行的ID
-> open_emp_id IN (SELECT emp_id FROM employee WHERE title='Teller' OR title = 'Head Teller');所有银行柜台员的ID
+------------+------------+---------+
| 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 |
+------------+------------+---------+
我们也可以利用内部连接的方式,使得子查询返回两列的单一查询来替代返回一个单列的两个子查询。
SELECT account_id, product_cd, cust_id FROM account WHERE (open_branch_id, open_emp_id) IN
-> (SELECT b.branch_id, e.emp_id FROM branch b INNER JOIN employee e ON b.branch_id=e.assigned_branch_id
-> WHERE b.name='Woburn Branch' AND (e.title='Teller' OR e.title = 'HeadTeller'));
+------------+------------+---------+
| 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 |
+------------+------------+---------+
关联子查询
非关联:子查询独立于包含语句,意味着子查询可以单独执行,并检验结果
关联:子查询预付与包含语句并引用其一列或多列。与非关联不同,关联子查询不是在包含语句中执行前一次执行完毕,而是为每一候选行执行一次。
下面子查询最后引用c.cust_id使之具有关联性,它的执行必须依赖于包含语句查询提供的 c.cust_id。首先从customer表中检索出13行客户记录,接着为每个客户执行一次子查询,每次执行包含语句查询都要向子查询传递客户ID。若子查询返回2,则该行被添加到结果集。
SELECT c.cust_id, c.cust_type_cd, c.city FROM customer c WHERE 2 =
-> (SELECT COUNT(*) FROM account a WHERE a.cust_id=c.cust_id);
+---------+--------------+---------+
| cust_id | cust_type_cd | city |
+---------+--------------+---------+
| 2 | I | Woburn |
| 3 | I | Quincy |
| 6 | I | Waltham |
| 8 | I | Salem |
| 10 | B | Salem |
+---------+--------------+---------+
SELECT c.cust_id, c.cust_type_cd, c.city FROM customer c WHERE
-> (SELECT SUM(a.avail_balance) FROM account a WHERE a.cust_id=c.cust_id) BETWEEN 5000 AND 10000;
+---------+--------------+------------+
| cust_id | cust_type_cd | city |
+---------+--------------+------------+
| 4 | I | Waltham |
| 7 | I | Wilmington |
| 11 | B | Wilmington |
+---------+--------------+------------+
-
exists 运算符
exists运算符只关心存在关系而不在乎数量。下面查询检索在特定日期进行过交易的所有账户,并不关心进行多少次交易.同时也可以使用not exit 检查子查询返回行数是否为0
SELECT a.account_id, a.product_cd, a.cust_id, a.avail_balance FROM account a WHERE EXISTS
-> (SELECT 1 FROM transaction t WHERE t.account_id = a.account_id AND t.txn_date='2008-09-22');
Empty set (0.07 sec)
SELECT a.account_id, a.product_cd, a.cust_id, a.avail_balance FROM account a WHERE NOT EXISTS
-> (SELECT 1 FROM transaction t WHERE t.account_id = a.account_id AND t.txn_date='2008-09-22');
+------------+------------+---------+---------------+
| account_id | product_cd | cust_id | avail_balance |
+------------+------------+---------+---------------+
| 1 | CHK | 1 | 1057.75 |
| 2 | SAV | 1 | 500.00 |
| 3 | CD | 1 | 3000.00 |
| 4 | CHK | 2 | 2258.02 |
| 5 | SAV | 2 | 200.00 |
| 7 | CHK | 3 | 1057.75 |
| 8 | MM | 3 | 2212.50 |
| 10 | CHK | 4 | 534.12 |
| 11 | SAV | 4 | 767.77 |
| 12 | MM | 4 | 5487.09 |
| 13 | CHK | 5 | 2237.97 |
| 14 | CHK | 6 | 122.37 |
| 15 | CD | 6 | 10000.00 |
| 17 | CD | 7 | 5000.00 |
| 18 | CHK | 8 | 3487.19 |
| 19 | SAV | 8 | 387.99 |
| 21 | CHK | 9 | 125.67 |
| 22 | MM | 9 | 9345.55 |
| 23 | CD | 9 | 1500.00 |
| 24 | CHK | 10 | 23575.12 |
| 25 | BUS | 10 | 0.00 |
| 27 | BUS | 11 | 9345.55 |
| 28 | CHK | 12 | 38552.05 |
| 29 | SBL | 13 | 50000.00 |
+------------+------------+---------+---------------+
SELECT a.account_id, a.product_cd, a.cust_id FROM account a WHERE NOT EXISTS
-> (SELECT 1 FROM business b WHERE b.cust_id =a.cust_id);
+------------+------------+---------+
| account_id | product_cd | cust_id |
+------------+------------+---------+
| 1 | CHK | 1 |
| 2 | SAV | 1 |
| 3 | CD | 1 |
| 4 | CHK | 2 |
| 5 | SAV | 2 |
| 7 | CHK | 3 |
| 8 | MM | 3 |
| 10 | CHK | 4 |
| 11 | SAV | 4 |
| 12 | MM | 4 |
| 13 | CHK | 5 |
| 14 | CHK | 6 |
| 15 | CD | 6 |
| 17 | CD | 7 |
| 18 | CHK | 8 |
| 19 | SAV | 8 |
| 21 | CHK | 9 |
| 22 | MM | 9 |
| 23 | CD | 9 |
+------------+------------+---------+