一。非关联子查询
mysql> 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.5 |
| 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 |
| 18 | CHK | 8 | 3487.19 |
| 19 | SAV | 8 | 387.99 |
| 21 | CHK | 9 | 125.67 |
| 22 | MM | 9 | 9345.55 |
| 23 | CD | 9 | 1500 |
| 24 | CHK | 10 | 23575.12 |
| 25 | BUS | 10 | 0 |
| 28 | CHK | 12 | 38552.05 |
| 29 | SBL | 13 | 50000 |
+------------+------------+---------+---------------+
17 rows in set
in 和 not in 运算符号:
mysql> SELECT branch_id,name,city
from branch
where name in ('Headquarters','Quincy Branch')
-> ;
+-----------+---------------+---------+
| branch_id | name | city |
+-----------+---------------+---------+
| 1 | Headquarters | Waltham |
| 3 | Quincy Branch | Quincy |
+-----------+---------------+---------+
2 rows in set
关联子查询,exists
使用条件:
面向任务的子查询:
在用于生成报告或数据源的系统里,
分组子查询