為了從兩個表中擷取資料,我們有時會用JOIN将兩個表連接配接起來。通常有以下幾種連接配接方式:
JOIN or INNER JOIN(内連接配接) : 這兩個是相同的,要求兩邊表同時有對應的資料,傳回行,任何一邊缺失資料就不顯示。
LEFT JOIN(左外連接配接):即使右邊的表中沒有比對,也從左表傳回所有的行。
RIGHT JOIN(右外連接配接):即使左邊的表中沒有比對,也從右表傳回所有的行。
FULL JOIN(全外連接配接):隻要其中一個表中存在比對就傳回行。
如例,有grade表(課程号sn,分數scroe,學号id),student表(學号id,學生姓名name),要查詢學生的姓名和成績
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIw1mYuQDNwgTOyMDM10CN3YDOygDMzETNyATM4EDMy0SM2MTOwUTMvwFMxgTMwIzLcFjNzkDM1EzLcd2bsJ2Lc12bj5ycn9Gbi52YugTMwIzZtl2Lc9CX6MHc0RHaiojIsJye.bmp)
SELECT s.name,g.sn,g.score from student as s join grade as g on s.id = g.id
或者
SELECT s.name,g.sn,g.score from student as s inner join grade as g on s.id = g.id
結果集如下
當LEFT JOIN時,
SELECT s.name,g.sn,g.score from student as s left join grade as g on s.id = g.id
當RIGHT JOIN時,
SELECT s.name,g.sn,g.score from student as s right join grade as g on s.id = g.id
當FULL JOIN時,
SELECT s.name,g.sn,g.score from student as s full join grade as g on s.id = g.id
注意,兩個表連接配接時用on,在使用left join(right join或full join)時,on與where的差別是:
on條件是在生成臨時表時使用的條件,它不管on中的條件是否為真,都會傳回左邊表中的記錄
where條件是在臨時表生成好後再對臨時表進行過濾的條件,這時已經沒有left join的含義(必須傳回左邊表的記錄)了,條件不為真的就全部過濾掉。如下圖,
on條件為黃色部分
SELECT s.name,g.sn,g.score from student as s left join grade as g on s.id = g.id and score =90
SELECT s.name,g.sn,g.score from student as s left join grade as g on s.id = g.id where score =90