select *
from DEPARTMENTS,EMPLOYEES--檢視兩張表所有的欄位
select*
from DEPARTMENTS cross join EMPLOYEES--同上,corss join可略(改逗號)
會輸出兩張表的所有欄位,因為交叉表的關係,所以有些項目可能會重覆許多
結果太多,不貼了。
--Cross Joins
--兩邊表格可以不用有共同欄位
select last_name,DEPARTMENT_name
from EMPLOYEES , DEPARTMENTS--last_name(20筆)是EMPLOYEES的資料,DEPARTMENT_name(8筆)是DEPARTMENTS的資料
--結果是交叉相乘,因為兩個資料欄沒有對應的編號,所以只是變成每個資料都對應所有資料,共20*8=160筆資料,cross join 一樣可略(改逗號)
last_name | DEPARTMENT_name |
King | Administration |
Kochhar | Administration |
De Haan | Administration |
Hunold | Administration |
Ernst | Administration |
Lorentz | Administration |
Mourgos | Administration |
Rajs | Administration |
Davies | Administration |
Matos | Administration |
Vargas | Administration |
Zlotkey | Administration |
Abel | Administration |
Taylor | Administration |
Grant | Administration |
Whalen | Administration |
Hartstein | Administration |
Fay | Administration |
Higgins | Administration |
Gietz | Administration |
King | Marketing |
Kochhar | Marketing |
De Haan | Marketing |
Hunold | Marketing |
Ernst | Marketing |
Lorentz | Marketing |
Mourgos | Marketing |
Rajs | Marketing |
Davies | Marketing |
Matos | Marketing |
Vargas | Marketing |
Zlotkey | Marketing |
Abel | Marketing |
Taylor | Marketing |
Grant | Marketing |
Whalen | Marketing |
Hartstein | Marketing |
Fay | Marketing |
Higgins | Marketing |
Gietz | Marketing |
King | Shipping |
Kochhar | Shipping |
De Haan | Shipping |
Hunold | Shipping |
Ernst | Shipping |
Lorentz | Shipping |
Mourgos | Shipping |
Rajs | Shipping |
Davies | Shipping |
Matos | Shipping |
Vargas | Shipping |
Zlotkey | Shipping |
Abel | Shipping |
Taylor | Shipping |
Grant | Shipping |
Whalen | Shipping |
Hartstein | Shipping |
Fay | Shipping |
Higgins | Shipping |
Gietz | Shipping |
King | IT |
Kochhar | IT |
De Haan | IT |
Hunold | IT |
Ernst | IT |
Lorentz | IT |
Mourgos | IT |
Rajs | IT |
Davies | IT |
Matos | IT |
Vargas | IT |
Zlotkey | IT |
Abel | IT |
Taylor | IT |
Grant | IT |
Whalen | IT |
Hartstein | IT |
Fay | IT |
Higgins | IT |
Gietz | IT |
King | Sales |
Kochhar | Sales |
De Haan | Sales |
Hunold | Sales |
Ernst | Sales |
Lorentz | Sales |
Mourgos | Sales |
Rajs | Sales |
Davies | Sales |
Matos | Sales |
Vargas | Sales |
Zlotkey | Sales |
Abel | Sales |
Taylor | Sales |
Grant | Sales |
Whalen | Sales |
Hartstein | Sales |
Fay | Sales |
Higgins | Sales |
Gietz | Sales |
King | Executive |
Kochhar | Executive |
De Haan | Executive |
Hunold | Executive |
Ernst | Executive |
Lorentz | Executive |
Mourgos | Executive |
Rajs | Executive |
Davies | Executive |
Matos | Executive |
Vargas | Executive |
Zlotkey | Executive |
Abel | Executive |
Taylor | Executive |
Grant | Executive |
Whalen | Executive |
Hartstein | Executive |
Fay | Executive |
Higgins | Executive |
Gietz | Executive |
King | Accounting |
Kochhar | Accounting |
De Haan | Accounting |
Hunold | Accounting |
Ernst | Accounting |
Lorentz | Accounting |
Mourgos | Accounting |
Rajs | Accounting |
Davies | Accounting |
Matos | Accounting |
Vargas | Accounting |
Zlotkey | Accounting |
Abel | Accounting |
Taylor | Accounting |
Grant | Accounting |
Whalen | Accounting |
Hartstein | Accounting |
Fay | Accounting |
Higgins | Accounting |
Gietz | Accounting |
King | Contracting |
Kochhar | Contracting |
De Haan | Contracting |
Hunold | Contracting |
Ernst | Contracting |
Lorentz | Contracting |
Mourgos | Contracting |
Rajs | Contracting |
Davies | Contracting |
Matos | Contracting |
Vargas | Contracting |
Zlotkey | Contracting |
Abel | Contracting |
Taylor | Contracting |
Grant | Contracting |
Whalen | Contracting |
Hartstein | Contracting |
Fay | Contracting |
Higgins | Contracting |
Gietz | Contracting |
--Equijoins
select
EMPLOYEE_ID,last_name,location_id ,departments.DEPARTMENT_ID--DEPARTMENT_ID是兩邊的共有欄位,所以必須指定是要哪一邊的(departments)
from employees join DEPARTMENTS--join不可省略,有join就有on
on EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID--篩選條件:兩邊的DEPARTMENT_ID是一樣的
"on"指定合併的"表格.欄位"名稱
order by EMPLOYEE_ID --可以使用排序order by
EMPLOYEE_ID | last_name | location_id | DEPARTMENT_ID |
100 | King | 1700 | 90 |
101 | Kochhar | 1700 | 90 |
102 | De Haan | 1700 | 90 |
103 | Hunold | 1400 | 60 |
104 | Ernst | 1400 | 60 |
107 | Lorentz | 1400 | 60 |
124 | Mourgos | 1500 | 50 |
141 | Rajs | 1500 | 50 |
142 | Davies | 1500 | 50 |
143 | Matos | 1500 | 50 |
144 | Vargas | 1500 | 50 |
149 | Zlotkey | 2500 | 80 |
174 | Abel | 2500 | 80 |
176 | Taylor | 2500 | 80 |
200 | Whalen | 1700 | 10 |
201 | Hartstein | 1800 | 20 |
202 | Fay | 1800 | 20 |
205 | Higgins | 1700 | 110 |
206 | Gietz | 1700 | 110 |
留言列表