css

2013年8月6日 星期二

sql join

toys
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


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;


利用條件判斷的比較運算子,結合兩份資料表的紀錄;

只有連結紀錄符合條件時,才會回傳資料欄。




內部連結:Equijoin :檢視相等性的內部連結



SELECT b.boy,t.toy

from boys b

INNER JOIN

toys t

ON b.toy_id=t.toy_id



boys
boy_idboytoy_id
1Davey3
2Bobby2
3Richie1

toys
toy_id toy
1 hula hoop
2 balsa glider
3 toy soldiers
4 harmonica


equijoin   ON b.toy_id=t.toy_id
boytoy
Richiehula hoop
Bobbybalsa glider
Daveytoy soldiers



內部連結:Non-equijoin :檢查非相等性的內部連結

SELECT b.boy,t.toy

from boys b

INNER JOIN

toys t

ON b.toy_id <> t.toy_id


non-equijion  ON b.toy_id <> t.toy_id
boytoy
Daveyhula hoop
Bobbyhulahoop
Daveybalsa glider
Richiebalsa glider
Bobbytoy soldiers
Richietoy soldiers
Daveyharmonica
Bobbyharmonica
Richieharmonica




內部連結:自然聯結 Natural Join

利用相同資料欄名稱的內部聯結

select boys.boy,toys.toy

from boys

natural join

toys


natural join 
boytoy
Richiehula hoop
Bobbybalsa glider
Daveytoy soldiers

沒有留言:

張貼留言