今天寫程式時,為了查詢 RDB 資料庫中三個 Table 裡相關的值,本來是用一般 SQL 語法,但為了求所有部門內的人員,所以只好重新溫習一下 SQL 語法中的 Left Join。 為了讓自己不要忘記,一方面也可以讓大家瞭解一下;以下例的實例來說明,應該比較容易瞭解。Table 範例如下:
table : Users
userId | named | companyNo | deptNo |
0001 | Scott | c01 | d01 |
0002 | Tiger | c01 | d02 |
0003 | Jacky | c01 | d02 |
0004 | Polin | c01 | d03 |
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
companyNo | name |
c01 | ABC Corp. |
c02 | XYZ 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
companyNo | DeptNo | name |
c01 | d01 | Account |
c01 | d02 | Sales |
c01 | d03 | MIS |
c01 | d04 | HR |
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 的特色如下:
沒有留言:
張貼留言