目录
内连接
如前面的from子句中的表连接。该查询指示服务器使用employee.dept_id作为两个表的桥梁，从而实现在同一查询结果集中包含来自两个表的列。这种操作被称为连接。
如果想使结果只包含18行(每个雇员一行)，就要让employee.dept_id起到连接的作用，因此需要在from子句中加入on子句。如果一个表中的dept_id存在，而另一个表中dept_id不存在，那么相关行的连接就失败，结果集中不会包含该行，这叫内连接。 一般我们习惯在JOIN前添加关键字INNER。
SELECT e.fname,e.lname,d.name FROM employee e JOIN department d ON e.dept_id=d.dept_id;
SELECT e.fname,e.lname,d.name FROM employee e INNER JOIN department d ON e.dept_id=d.dept_id;
+----------+-----------+----------------+
| fname | lname | name |
+----------+-----------+----------------+
| Susan | Hawthorne | Operations |
| Helen | Fleming | Operations |
| Chris | Tucker | Operations |
| Sarah | Parker | Operations |
| Jane | Grossman | Operations |
| Paula | Roberts | Operations |
| Thomas | Ziegler | Operations |
| Samantha | Jameson | Operations |
| John | Blake | Operations |
| Cindy | Mason | Operations |
| Frank | Portman | Operations |
| Theresa | Markham | Operations |
| Beth | Fowler | Operations |
| Rick | Tulman | Operations |
| John | Gooding | Loans |
| Michael | Smith | Administration |
| Susan | Barker | Administration |
| Robert | Tyler | Administration |
+----------+-----------+----------------+
下面的查询需要同时显示employee的emp_id,fname,lname和department的name数据，因此两个表都在from子句中。利用INNER JOIN方式机制连接两个表。数据库利用employee表中的dept_id列值在department表中查找关联部门名称。两个表的连接条件由from子句中的on子句指定
SELECT employee.emp_id,employee.fname,employee.lname,department.name dept_name
-> FROM employee INNER JOIN department ON employee.dept_id=department.dept_id;
+--------+----------+-----------+----------------+
| emp_id | fname | lname | dept_name |
+--------+----------+-----------+----------------+
| 4 | Susan | Hawthorne | Operations |
| 6 | Helen | Fleming | Operations |
| 7 | Chris | Tucker | Operations |
| 8 | Sarah | Parker | Operations |
| 9 | Jane | Grossman | Operations |
| 10 | Paula | Roberts | Operations |
| 11 | Thomas | Ziegler | Operations |
| 12 | Samantha | Jameson | Operations |
| 13 | John | Blake | Operations |
| 14 | Cindy | Mason | Operations |
| 15 | Frank | Portman | Operations |
| 16 | Theresa | Markham | Operations |
| 17 | Beth | Fowler | Operations |
| 18 | Rick | Tulman | Operations |
| 5 | John | Gooding | Loans |
| 1 | Michael | Smith | Administration |
| 2 | Susan | Barker | Administration |
| 3 | Robert | Tyler | Administration |
+--------+----------+-----------+----------------+
-
连接3个以上表
对于3个表的连接，在from子句中将包含3个表和两种连接类型，以及两个on子句。
SELECT a.account_id, c.fed_id,e.fname,e.lname FROM account a
-> INNER JOIN customer c ON a.cust_id=c.cust_id
-> INNER JOIN employee e ON a.open_emp_id=e.emp_id
-> WHERE c.cust_type_cd='B';
+------------+------------+---------+---------+
| account_id | fed_id | fname | lname |
+------------+------------+---------+---------+
| 24 | 04-1111111 | Theresa | Markham |
| 25 | 04-1111111 | Theresa | Markham |
| 27 | 04-2222222 | Paula | Roberts |
| 28 | 04-3333333 | Theresa | Markham |
| 29 | 04-4444444 | John | Blake |
+------------+------------+---------+---------+
连续两次使用同一个表，为了使之正常工作，需要给每个重复使用的地方定义不同的别名。
SELECT a.account_id,e.emp_id,b_a.name open_branch,b_e.name emp_branch FROM account a
-> INNER JOIN branch b_a ON a.open_branch_id=b_a.branch_id
-> INNER JOIN employee e ON a.open_emp_id=e.emp_id
-> INNER JOIN branch b_e ON e.assigned_branch_id = b_e.branch_id
-> WHERE a.product_cd='CHK';
+------------+--------+---------------+---------------+
| account_id | emp_id | open_branch | emp_branch |
+------------+--------+---------------+---------------+
| 1 | 10 | Woburn Branch | Woburn Branch |
| 4 | 10 | Woburn Branch | Woburn Branch |
| 7 | 13 | Quincy Branch | Quincy Branch |
| 10 | 1 | Headquarters | Headquarters |
| 13 | 16 | So. NH Branch | So. NH Branch |
| 14 | 1 | Headquarters | Headquarters |
| 18 | 16 | So. NH Branch | So. NH Branch |
| 21 | 1 | Headquarters | Headquarters |
| 24 | 16 | So. NH Branch | So. NH Branch |
| 28 | 16 | So. NH Branch | So. NH Branch |
+------------+--------+---------------+---------------+
-
自连接
employee表包含了一个自身的外键，即指向本表主键的列superior_emp_id，该列指向雇员的主管。使用自连接可以在列出每个雇员同时列出主管姓名。
SELECT e.fname, e.lname,e_mgr.fname mgr_fname,e_mgr.lname mgr_lname FROM employee e
-> INNER JOIN employee e_mgr ON e.superior_emp_id=e_mgr.emp_id;
+----------+-----------+-----------+-----------+
| fname | lname | mgr_fname | mgr_lname |
+----------+-----------+-----------+-----------+
| Susan | Barker | Michael | Smith |
| Robert | Tyler | Michael | Smith |
| Susan | Hawthorne | Robert | Tyler |
| John | Gooding | Susan | Hawthorne |
| Helen | Fleming | Susan | Hawthorne |
| Chris | Tucker | Helen | Fleming |
| Sarah | Parker | Helen | Fleming |
| Jane | Grossman | Helen | Fleming |
| Paula | Roberts | Susan | Hawthorne |
| Thomas | Ziegler | Paula | Roberts |
| Samantha | Jameson | Paula | Roberts |
| John | Blake | Susan | Hawthorne |
| Cindy | Mason | John | Blake |
| Frank | Portman | John | Blake |
| Theresa | Markham | Susan | Hawthorne |
| Beth | Fowler | Theresa | Markham |
| Rick | Tulman | Theresa | Markham |
+----------+-----------+-----------+-----------+
-
不等连接
对表自身使用不等连接。举例来说，假如经理举办一次面向银行柜员的象棋比赛，被要求创建所有对弈着列表，这就需要为所有柜员title=‘Teller’进行employee的自连接，并返回所有emp_id不同的行，自己与自己无法下棋。
SELECT e1.fname,e1.lname, 'VS' vs, e2.fname, e2.lname FROM employee e1
-> INNER JOIN employee e2 ON e1.emp_id != e2.emp_id
-> WHERE e1.title='Teller' AND e2.title = 'Teller';
+----------+----------+----+----------+----------+
| fname | lname | vs | fname | lname |
+----------+----------+----+----------+----------+
| Sarah | Parker | VS | Chris | Tucker |
| Jane | Grossman | VS | Chris | Tucker |
| Thomas | Ziegler | VS | Chris | Tucker |
| Samantha | Jameson | VS | Chris | Tucker |
| Cindy | Mason | VS | Chris | Tucker |
| Frank | Portman | VS | Chris | Tucker |
| Beth | Fowler | VS | Chris | Tucker |
| Rick | Tulman | VS | Chris | Tucker |
| Chris | Tucker | VS | Sarah | Parker |
[...]这里还存在一个错误，即每对选手会出现两次(1vs2、2vs1）。可以使用连接条件e1.emp_id > e2.emp_id 来获得期望结果。
SELECT e1.fname,e1.lname, 'VS' vs, e2.fname, e2.lname FROM employee e1
-> INNER JOIN employee e2 ON e1.emp_id > e2.emp_id
-> WHERE e1.title='Teller' AND e2.title = 'Teller';构建查找，查找所有主管位于另一个部门的雇员，获取雇员的ID、姓名
SELECT e.emp_id, e.fname, e.lname FROM employee e INNER JOIN employee mgr ON e.superior_emp_id=mgr.emp_id
-> WHERE e.dept_id != mgr.dept_id;
+--------+-------+-----------+
| emp_id | fname | lname |
+--------+-------+-----------+
| 4 | Susan | Hawthorne |
| 5 | John | Gooding |
+--------+-------+-----------+
外连接
-
匹配遗漏问题
之前的表连接没有考虑连接条件可能无法为表中所有行匹配的问题，则一个表或者其他表中某些行列就遗漏在结果集之外。下面account表中有24个账户，13个不同客户ID为1~13,每个客户至少有一个账户。customer表中的ID共13行，充分包含account表中数据，期望结果应为24行。
SELECT account_id, cust_id FROM account;
+------------+---------+
| account_id | cust_id |
+------------+---------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
| 7 | 3 |
| 8 | 3 |
| 10 | 4 |
| 11 | 4 |
| 12 | 4 |
| 13 | 5 |
| 14 | 6 |
| 15 | 6 |
| 17 | 7 |
| 18 | 8 |
| 19 | 8 |
| 21 | 9 |
| 22 | 9 |
| 23 | 9 |
| 24 | 10 |
| 25 | 10 |
| 27 | 11 |
| 28 | 12 |
| 29 | 13 |
+------------+---------+
SELECT cust_id FROM customer;
+---------+
| cust_id |
+---------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
+---------+
SELECT a.account_id, c.cust_id FROM account a INNER JOIN customer c ON a.cust_id=c.cust_id;
+------------+---------+
| account_id | cust_id |
+------------+---------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
| 7 | 3 |
| 8 | 3 |
| 10 | 4 |
| 11 | 4 |
| 12 | 4 |
| 13 | 5 |
| 14 | 6 |
| 15 | 6 |
| 17 | 7 |
| 18 | 8 |
| 19 | 8 |
| 21 | 9 |
| 22 | 9 |
| 23 | 9 |
| 24 | 10 |
| 25 | 10 |
| 27 | 11 |
| 28 | 12 |
| 29 | 13 |
+------------+---------+但是我们将account表连接到business表格时，输出仅有4行。因为business中只包含4个account表中账户，采用内连接，其他用户全部被遗漏。
SELECT cust_id, name FROM business;
+---------+------------------------+
| cust_id | name |
+---------+------------------------+
| 10 | Chilton Engineering |
| 11 | Northeast Cooling Inc. |
| 12 | Superior Auto Body |
| 13 | AAA Insurance Inc. |
+---------+------------------------+
SELECT a.account_id, b.cust_id, b.name FROM account a INNER JOIN business b ON a.cust_id=b.cust_id;
+------------+---------+------------------------+
| account_id | cust_id | name |
+------------+---------+------------------------+
| 24 | 10 | Chilton Engineering |
| 25 | 10 | Chilton Engineering |
| 27 | 11 | Northeast Cooling Inc. |
| 28 | 12 | Superior Auto Body |
| 29 | 13 | AAA Insurance Inc. |
+------------+---------+------------------------+-
左外连接与右外连接
如果想查询返回所有账户，就在两个表之间建立外连接。这里使用left outer join。关键词left指出连接左边的表决定结果集的行数，右边的只负责提供与之对应匹配的值
SELECT a.account_id, a.cust_id, b.name FROM account a LEFT OUTER JOIN business b ON a.cust_id=b.cust_id;
+------------+---------+------------------------+
| account_id | cust_id | name |
+------------+---------+------------------------+
| 1 | 1 | NULL |
| 2 | 1 | NULL |
| 3 | 1 | NULL |
| 4 | 2 | NULL |
| 5 | 2 | NULL |
| 7 | 3 | NULL |
| 8 | 3 | NULL |
| 10 | 4 | NULL |
| 11 | 4 | NULL |
| 12 | 4 | NULL |
| 13 | 5 | NULL |
| 14 | 6 | NULL |
| 15 | 6 | NULL |
| 17 | 7 | NULL |
| 18 | 8 | NULL |
| 19 | 8 | NULL |
| 21 | 9 | NULL |
| 22 | 9 | NULL |
| 23 | 9 | NULL |
| 24 | 10 | Chilton Engineering |
| 25 | 10 | Chilton Engineering |
| 27 | 11 | Northeast Cooling Inc. |
| 28 | 12 | Superior Auto Body |
| 29 | 13 | AAA Insurance Inc. |
+------------+---------+------------------------+如果指定的是right outer join，结果如下
SELECT c.cust_id, b.name FROM customer c RIGHT OUTER JOIN business b ON c.cust_id=b.cust_id;
+---------+------------------------+
| cust_id | name |
+---------+------------------------+
| 10 | Chilton Engineering |
| 11 | Northeast Cooling Inc. |
| 12 | Superior Auto Body |
| 13 | AAA Insurance Inc. |
+---------+------------------------+-
三路外连接
将一个表与其他两个表进行外连接。例如得到所有账户列表，其中包含个人客户的姓名以及商业客户的企业名称
SELECT a.account_id, a.product_cd, CONCAT(i.fname,' ', i.lname) person_name, b.name business_name
-> FROM account a LEFT OUTER JOIN individual i ON a.cust_id=i.cust_id
-> LEFT OUTER JOIN business b ON a.cust_id = b.cust_id;
+------------+------------+-----------------+------------------------+
| account_id | product_cd | person_name | business_name |
+------------+------------+-----------------+------------------------+
| 1 | CHK | James Hadley | NULL |
| 2 | SAV | James Hadley | NULL |
| 3 | CD | James Hadley | NULL |
| 4 | CHK | Susan Tingley | NULL |
| 5 | SAV | Susan Tingley | NULL |
| 7 | CHK | Frank Tucker | NULL |
| 8 | MM | Frank Tucker | NULL |
| 10 | CHK | John Hayward | NULL |
| 11 | SAV | John Hayward | NULL |
| 12 | MM | John Hayward | NULL |
| 13 | CHK | Charles Frasier | NULL |
| 14 | CHK | John Spencer | NULL |
| 15 | CD | John Spencer | NULL |
| 17 | CD | Margaret Young | NULL |
| 18 | CHK | George Blake | NULL |
| 19 | SAV | George Blake | NULL |
| 21 | CHK | Richard Farley | NULL |
| 22 | MM | Richard Farley | NULL |
| 23 | CD | Richard Farley | NULL |
| 24 | CHK | NULL | Chilton Engineering |
| 25 | BUS | NULL | Chilton Engineering |
| 27 | BUS | NULL | Northeast Cooling Inc. |
| 28 | CHK | NULL | Superior Auto Body |
| 29 | SBL | NULL | AAA Insurance Inc. |
+------------+------------+-----------------+------------------------+
-
自外连接
在前面的自连接中，生成雇员和他们主管的列表。但是没有主管的雇员将被遗漏到结果集之外。将内连接变为外连接，结果集就包括所有雇员。
SELECT e.fname, e.lname, e_mgr.fname mgr_fname, e_mgr.lname mgr_lname FROM employee e
-> LEFT OUTER JOIN employee e_mgr ON e.superior_emp_id = e_mgr.emp_id;
+----------+-----------+-----------+-----------+
| fname | lname | mgr_fname | mgr_lname |
+----------+-----------+-----------+-----------+
| Michael | Smith | NULL | NULL |
| Susan | Barker | Michael | Smith |
| Robert | Tyler | Michael | Smith |
| Susan | Hawthorne | Robert | Tyler |
| John | Gooding | Susan | Hawthorne |
| Helen | Fleming | Susan | Hawthorne |
| Chris | Tucker | Helen | Fleming |
| Sarah | Parker | Helen | Fleming |
| Jane | Grossman | Helen | Fleming |
| Paula | Roberts | Susan | Hawthorne |
| Thomas | Ziegler | Paula | Roberts |
| Samantha | Jameson | Paula | Roberts |
| John | Blake | Susan | Hawthorne |
| Cindy | Mason | John | Blake |
| Frank | Portman | John | Blake |
| Theresa | Markham | Susan | Hawthorne |
| Beth | Fowler | Theresa | Markham |
| Rick | Tulman | Theresa | Markham |
+----------+-----------+-----------+-----------+银行总裁Michael Smith都包括在内，使用左连接生成所有雇员及可能主管。改为右连接，生成除了每个主管还有其管理的雇员集合。
Michael Smith作为 Susan Barker 和Robert Tyler的主管出现两次，Susan Barker显示一次但他不是任何人的主管。所有18个雇员在第三列和第四列出现至少一次，其中管理雇员超过一人的会出现不止一次。
SELECT e.fname, e.lname, e_mgr.fname mgr_fname, e_mgr.lname mgr_lname FROM employee e
-> RIGHT OUTER JOIN employee e_mgr ON e.superior_emp_id = e_mgr.emp_id;
+----------+-----------+-----------+-----------+
| fname | lname | mgr_fname | mgr_lname |
+----------+-----------+-----------+-----------+
| Susan | Barker | Michael | Smith |
| Robert | Tyler | Michael | Smith |
| NULL | NULL | Susan | Barker |
| Susan | Hawthorne | Robert | Tyler |
| John | Gooding | Susan | Hawthorne |
| Helen | Fleming | Susan | Hawthorne |
| Paula | Roberts | Susan | Hawthorne |
| John | Blake | Susan | Hawthorne |
| Theresa | Markham | Susan | Hawthorne |
| NULL | NULL | John | Gooding |
| Chris | Tucker | Helen | Fleming |
| Sarah | Parker | Helen | Fleming |
| Jane | Grossman | Helen | Fleming |
| NULL | NULL | Chris | Tucker |
| NULL | NULL | Sarah | Parker |
| NULL | NULL | Jane | Grossman |
| Thomas | Ziegler | Paula | Roberts |
| Samantha | Jameson | Paula | Roberts |
| NULL | NULL | Thomas | Ziegler |
| NULL | NULL | Samantha | Jameson |
| Cindy | Mason | John | Blake |
| Frank | Portman | John | Blake |
| NULL | NULL | Cindy | Mason |
| NULL | NULL | Frank | Portman |
| Beth | Fowler | Theresa | Markham |
| Rick | Tulman | Theresa | Markham |
| NULL | NULL | Beth | Fowler |
| NULL | NULL | Rick | Tulman |
+----------+-----------+-----------+-----------+交叉连接
最简单的连接方式是直接在from子句中加入employee和department表。e表中有18个雇员，d表中有3个部门，由于没有指定表的连接方式，数据库将产生笛卡尔积，即18*3=54个置换。这种连接称为交叉连接。
SELECT e.fname,e.lname,d.name FROM employee e JOIN department d;
+----------+-----------+----------------+
| fname | lname | name |
+----------+-----------+----------------+
| Michael | Smith | Operations |
| Michael | Smith | Loans |
| Michael | Smith | Administration |
| Susan | Barker | Operations |
| Susan | Barker | Loans |
| Susan | Barker | Administration |
| Robert | Tyler | Operations |
| Robert | Tyler | Loans |
| Robert | Tyler | Administration |
[...]
扫一扫
5277
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
