學習筆記專區

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