SQL學習筆記7——SQL中檢索數據之連接查詢

含義

連接查詢又稱多表查詢，當查詢的字段來自于多個表時，就會用到連接查詢

語法

select 字段1，字段2
from 表1，表2,...;

• 笛卡爾乘積：當查詢多個表時，沒有添加有效的連接條件，導致多個表所有行實現完全連接
• 如何解決：添加有效的連接條件

  select 字段1，字段2
  from 表1，表2,...
  where 連接條件;
  

  例子

  SELECT name,boyname
  FROM boys,beauty
  WHERE beauty.boyfriend_id=boys.id;
  

分類

• 按年代分類：
  • sql92：僅僅支持內連接
  • sql99【推薦使用】：支持內連接+外連接（左外和右外）+交叉連接
• 按功能分類
  • 內連接：等值 非等值 自連接
  • 外連接：左外 右外 全外 交叉連接

sql92語法

1. 等值連接
   語法：

   select 查詢列表
   from 表1 別名,表2 別名
   where 表1.key=表2.key
   【and 篩選條件】
   【group by 分組字段】
   【having 分組后的篩選】
   【order by 排序字段】
   

   特點：
   ①一般為表起別名：提高語句的簡潔度、區分多個重名的字段
   ②多表的順序可以調換
   ③n表連接至少需要n-1個連接條件
   ④等值連接的結果是多表的交集部分
   ⑤如果為表起了別名，則查詢的字段就不能使用原來的表名去限定
   ⑥可以搭配前面介紹的所有子句使用，如排序、分組、篩選
2. 非等值連接
   語法：

   select 查詢列表
   from 表1 別名,表2 別名
   where 非等值的連接條件
   【and 篩選條件】
   【group by 分組字段】
   【having 分組后的篩選】
   【order by 排序字段】
   

3. 自連接
   語法：

   select 查詢列表
   from 表 別名1,表 別名2
   where 等值的連接條件
   【and 篩選條件】
   【group by 分組字段】
   【having 分組后的篩選】
   【order by 排序字段】
   

SQL99語法

語法

select 查詢列表
from 表1 別名 [連接類型]
join 表2 別名
on 連接條件
[where 篩選條件]
[group by 分組列表]
[having 分組后的篩選]
[order by 排序列表]
[limit 子句];
/*
內連接:連接類型是inner
外連接：
左外：left [outer]
右外：right [outer]
全外：full [outer]
交叉連接: cross
*/

內連接

select 查詢列表
from 表1 別名
inner join 表2 別名
on 連接條件
[where 篩選條件]
[group by 分組列表]
[having 分組后的篩選]
[order by 排序列表]
[limit 子句];

1. 等值連接
   查詢員工名、部門名

   SELECT last_name,department_name
   FROM departments d
   INNER JOIN employees e
   on e.department_id = d.department_id
   

2. 非等值連接
   連接條件修改為相應的非等值連接條件即可
3. 自連接

   SELECT e.last_name,m.last_name
   FROM employees e
   JOIN employees m
   ON e.manager_id=m.employee_id;
   

特點

• 可添加排序、分組、篩選
• inner可以省略
• 篩選條件放在where后面，連接條件放在on后面，提高分離性，便于閱讀
• inner join的表必須和from的表有連接條件

外連接

特點

• 查詢的結果=主表中所有的行，如果從表和它匹配的將顯示匹配行，如果從表沒有匹配的則顯示null
• left join 左邊的就是主表，right join 右邊的就是主表
  full join 兩邊都是主表
• 一般用于查詢除了交集部分的剩余的不匹配的行
• 左外和右外交換兩個表的順序，可以實現相同的效果
• 全外連接=內連接的結果+表1中有但表2中沒有的+表2中有但表1中沒有的(沒有的用null填充)
• MySQL中不支持全外連接

例子：

1. 查詢男朋友不在男神表的女神名

   USE girls;
   SELECT b.name,bo.*
   FROM beauty b
   LEFT OUTER JOIN boys bo
   ON b.boyfriend_id=bo.id
   WHERE bo.id IS NULL;
   /*
   或者按下面等價的方式
   USE girls;
   SELECT b.name,bo.*
   FROM boys bo
   RIGHT OUTER JOIN beauty b
   ON b.boyfriend_id=bo.id
   WHERE bo.id IS NULL;
   */
   

交叉連接

1. 語法：

select 查詢列表
from 表1 別名
cross join 表2 別名;

2. 特點：
   相當于笛卡爾乘積

SQL92 vs SQL99