廣告聯播

2008年6月18日 星期三

[教學]SQL 語法 JOIN



  今天寫程式時,為了查詢 RDB 資料庫中三個 Table 裡相關的值,本來是用一般 SQL 語法,但為了求所有部門內的人員,所以只好重新溫習一下 SQL 語法中的 Left Join。 為了讓自己不要忘記,一方面也可以讓大家瞭解一下;以下例的實例來說明,應該比較容易瞭解。Table 範例如下:

table : Users

userIdnamedcompanyNodeptNo
0001Scottc01d01
0002Tigerc01d02
0003Jackyc01d02
0004Polinc01d03

SQL:
create table users ( userId char(4), name varchar2(16), companyNo char(3), DeptNo char(3) );
insert into users values( '0001' , 'Scott', 'c01' , 'd01' );
insert into users values( '0002' , 'Tiger', 'c01' , 'd02' );
insert into users values( '0003' , 'Jacky' , 'c01' , 'd02' );
insert into users values( '0004' , 'Polin', 'c01' , 'd03' );

table : Companys

companyNoname
c01ABC Corp.
c02XYZ Corp.


SQL:
create table Company ( companyNo char(3), name varchar2(16) );
insert into Company values( 'c01' , 'ABC Corp.' );
insert into Company values( 'c02' , 'XYZ Corp.' );

table : Depts

companyNoDeptNoname
c01d01Account
c01d02Sales
c01d03MIS
c01d04HR


SQL:
create table Depts ( companyNo char(3), deptNo char(3), name varchar2(16) );
insert into Depts values( 'c01' ,'d01' , 'Account' );
insert into Depts values( 'c01' ,'d02' , 'Sales' );
insert into Depts values( 'c01' ,'d03' , 'MIS' );
insert into Depts values( 'c01' ,'d04' , 'HR' );

需求一: User 屬於那一個公司,那一個部門的 SQL:

SELECT u.*, d.name as deptName, c.name as companyName
FROM users u , depts d, company c
WHERE 1=1
AND u.deptNo = d.deptNo
AND u.companyNo = c.companyNo

結果:

USERID

NAME

COMPANYNO

DEPTNO

DEPTNAME

COMPANYNAME

0001

Scott

c01

d01

Account

ABC Corp.

0003

Jacky

c01

d02

Sales

ABC Corp.

0002

Tiger

c01

d02

Sales

ABC Corp.

0004

Polin

c01

d03

MIS

ABC Corp.



需求二:列出所有部門的人員,部門若沒有人員也要列出。

SELECT d.*, u.name as userName , c.name as companyName
FROM depts d
LEFT JOIN users u ON d.deptNo = u.deptNo
INNER JOIN company c ON d.companyNO = c.companyNo

結果:

COMPANYNO

DEPTNO

NAME

USERNAME

COMPANYNAME

c01

d01

Account

Scott

ABC Corp.

c01

d02

Sales

Jacky

ABC Corp.

c01

d02

Sales

Tiger

ABC Corp.

c01

d03

MIS

Polin

ABC Corp.

c01

d04

HR

(null)

ABC Corp.


PS: 若仍用需求一的方法,則無法顯示 d04 HR 部門

SQL:
SELECT d.*, u.name as userName , c.name as companyName
FROM depts d, users u, company c
WHERE 1=1
AND d.deptno = u.deptno
AND d.companyno = c.companyno
結果:

COMPANYNO

DEPTNO

NAME

USERNAME

COMPANYNAME

c01

d01

Account

Scott

ABC Corp.

c01

d02

Sales

Jacky

ABC Corp.

c01

d02

Sales

Tiger

ABC Corp.

c01

d03

MIS

Polin

ABC Corp.



整理一下這三個 JOIN 的特色如下:
  • INNER JOIN - 兩邊的資料表擁有相同的優先權, 要兩邊都有的資料才會被包含在新的資料表.
  • RIGHT JOIN - 右邊的資料表擁有優先權, 右邊所有的資料都會被包含, 而左邊只有符合的資料才會被包含.
  • LEFT JOIN - 左邊的資料表擁有優先權, 左邊所有的資料都會被包含, 而右邊只有符合的資料才會被包含.
  • 沒有留言:

    張貼留言