SQL中的連接(極客時間)

SQL中的連接

關係型資料庫的核心之一就是連接, 而在不同的標準中, 連接的寫法上可能有區別, 最為主要的兩個SQL標準就是SQL92和SQL99了, 後面的數字表示的是標準提出的時間.

SQL92中的連接

案例使用的表是球員表, 球隊表和身高級別表, 下載: https://github.com/cystanford/sql_nba_data

笛卡爾積

笛卡爾積是一個數學運算, 假設兩個集合X和Y, 那麼X和Y的笛卡爾積就是X和Y的所有可能組合.

SQL: SELECT * FROM player, team; // 笛卡爾積

簡單的來說就是兩個表的數據行的乘積就是結果行.

等值連接

等值連接就是用兩張表都存在的列進行連接, 可以對多張表進行等值連接.

SELECT player_id, player.team_id, player_name, height, team_name FROM player, team WHERE player.team_id = team.team_id; //等值連接

SELECT player_id, a.team_id, player_name, height, team_name FROM player AS a, team AS b WHERE a.team_id = b.team_id; // 別名代替, 看上去會更簡潔

非等值連接

當我們進行多表查詢的時候，如果連接多個表的條件是等號時(如果能使用等號,肯定是存在相同的列)，就是等值連接，其他的運算符連接就是非等值查詢。

現在要查詢每個球員的身高級別, 可以採用非等值連接查詢:

SELECT p.player_name, p.height, h.height_level
FROM player AS p, height_grades AS h
WHERE p.height BETWEEN h.height_lowest AND h.height_highest; // 查詢每個球員的身高級別

外連接

外連接是查詢某一方不滿足條件的記錄. 兩張表的外連接, 會有一張是主表, 一張是從表. 多張表的外連接, 第一張表是主表, 剩下的表全是從表, 在SQL92中採用(+)代表從表所在的位置, 並且在SQL92中只有左外連接和右外連接, 沒有全外連接.

左外連接就是左邊的表是主表, 需要顯示全部行, 右側的表是從表. 右外連接則相反.

SELECT * FROM player, team where player.team_id = team.team_id(+); // 左外連接

SELECT * FROM player, team where player.team_id(+) = team.team_id; // 右外連接

不過筆者在測試的時候發現(+)是不能使用的, 所以請參考下麵的SQL99的寫法

SQL：SELECT * FROM player LEFT JOIN team on player.team_id = team.team_id; // 左外連接
SQL：SELECT * FROM player RIGHT JOIN team on player.team_id = team.team_id; // 右外連接

自連接

自連接可以對多個表進行操作，也可以對同一個表進行操作。也就是說查詢條件使用了當前表的欄位(不過按照筆者的理解就是自己和自己連接查詢)。

SELECT b.player_name, b.height FROM player as a , player as b WHERE a.player_name = '佈雷克 - 格裡芬' and a.height < b.height; // 自連接查詢比佈雷克 - 格裡芬身高高的球員

SQL99的連接

還是上面的表, 看看SQL99中是如何使用連接的.

交叉連接

交叉連接也就是SQL92中的笛卡爾積, 這裡採用CROSS JOIN, 如下可以得到笛卡爾積的結果:

SELECT * FROM player CROSS JOIN team;  // 使用CROSS JOIN實現笛卡爾積

自然連接

自然連接就是SQL92中的等值連接, 會自動查詢連接表中所有相同的欄位, 進行等值連接.

SELECT player_id, team_id, player_name, height, team_name FROM player NATURAL JOIN team ; // 使用NATURAL JOIN可以自動查詢連接表的相同欄位

這個有缺點有優點, 缺點在於如果設計表的時候設計不規範, 可能造成兩張表欄位名相同但表達意義不同, 這個時候直接使用NATURAL JOIN出來的結果可能就不是想要的結果. 當然優點在於寫法簡單

ON連接

ON連接用來指定想要的連接條件, 同樣可以幫助我們實現自然連接的功能

SELECT player_id, player.team_id, player_name, height, team_name FROM player JOIN team ON player.team_id = team.team_id; // ON連接實現自然連接

ON連接也可以進行非等值連接, 如下

SELECT p.player_name, p.height, h.height_level
FROM player as p JOIN height_grades as h
ON height BETWEEN h.height_lowest AND h.height_highest; // 查詢球員的身高等級

USING連接

USING指定數據表裡同名欄位進行等值連接

SELECT player_id, team_id, player_name, height, team_name FROM player JOIN team USING(team_id); 

優點缺點與用NATURAL JOIN一樣

外連接

SQL99中的外連接:

1. 左外連接: LEFT JOIN或LEFT OUTER JOIN
2. 右外連接: RIGHT JOIN或RIGHT OUTER JOIN
3. 全外連接: FULL JOIN或FULL OUTER JOIN

SELECT * FROM player LEFT JOIN team ON player.team_id = team.team_id; // 左外連接
SELECT * FROM player RIGHT JOIN team ON player.team_id = team.team_id; // 右外連接
SELECT * FROM player FULL JOIN team ON player.team_id = team.team_id; // 全外連接

需要註意的是 MySQL 不支持全外連接，否則的話全外連接會返回左表和右表中的所有行。當表之間有匹配的行，會顯示內連接的結果。當某行在另一個表中沒有匹配時，那麼會把另一個表中選擇的列顯示為空值。

也就是說，全外連接的結果 = 左右表匹配的數據 + 左表沒有匹配到的數據 + 右表沒有匹配到的數據。

自連接

自連接在SQL99中的表述如下:

SELECT b.player_name, b.height FROM player as a JOIN player as b ON a.player_name = '佈雷克 - 格裡芬' and a.height < b.height; // 自連接

建議使用SQL99的寫法, 看上去會更簡潔與清晰明瞭.