| toy_id | toy |
|---|---|
| 1 | hula hoop |
| 2 | balsa glider |
| 3 | toy soldiers |
| 4 | harmonica |
boys
| boy_id | boy |
|---|---|
| 1 | Davey |
| 2 | Bobby |
| 3 | Richie |
交叉連結 CROSS JOIN ,卡笛生乘積,交叉乘積,不是連結NO JOIN
SELECT t.toy, b.boy SELECT t.toy, b.boy
FROM toys t or FROM toys t,boys b
CROSS JOIN boys b
FROM toys t or FROM toys t,boys b
CROSS JOIN boys b
| toy | boy |
|---|---|
| hula hoop | Davey |
| hula hoop | Bobby |
| hula hoop | Richie |
| balsa glider | Davey |
| balsa glider | Bobby |
| balsa glider | Richie |
| toy soldiers | Davey |
| toy soldiers | Bobby |
| toy soldiers | Richie |
| harmonica | Davey |
| harmonica | Bobby |
| harmonica | Richie |
內部連結 INNER JOIN
SELECT somecolumns
FROM table1
INNER JOIN
table2
ON somecondition;
FROM table1
INNER JOIN
table2
ON somecondition;
利用條件判斷的比較運算子,結合兩份資料表的紀錄;
只有連結紀錄符合條件時,才會回傳資料欄。
內部連結:Equijoin :檢視相等性的內部連結
SELECT b.boy,t.toy
from boys b
INNER JOIN
toys t
ON b.toy_id=t.toy_id
from boys b
INNER JOIN
toys t
ON b.toy_id=t.toy_id
boys
| boy_id | boy | toy_id |
|---|---|---|
| 1 | Davey | 3 |
| 2 | Bobby | 2 |
| 3 | Richie | 1 |
toys
| toy_id | toy |
|---|---|
| 1 | hula hoop |
| 2 | balsa glider |
| 3 | toy soldiers |
| 4 | harmonica |
equijoin ON b.toy_id=t.toy_id
| boy | toy |
|---|---|
| Richie | hula hoop |
| Bobby | balsa glider |
| Davey | toy soldiers |
內部連結:Non-equijoin :檢查非相等性的內部連結
SELECT b.boy,t.toy
from boys b
INNER JOIN
toys t
ON b.toy_id <> t.toy_id
| boy | toy |
|---|---|
| Davey | hula hoop |
| Bobby | hulahoop |
| Davey | balsa glider |
| Richie | balsa glider |
| Bobby | toy soldiers |
| Richie | toy soldiers |
| Davey | harmonica |
| Bobby | harmonica |
| Richie | harmonica |
內部連結:自然聯結 Natural Join
利用相同資料欄名稱的內部聯結
select boys.boy,toys.toy
from boys
natural join
toys
| boy | toy |
|---|---|
| Richie | hula hoop |
| Bobby | balsa glider |
| Davey | toy soldiers |
