表的聯結、運算子學習筆記

lnwxzyp發表於2010-12-01

表的聯結：

1.等值聯結

兩個表的相同列的值必須相等。

等值聯結也稱為簡單聯結或內聯結

SELECT
   e.last_name,
   e.department_id,
   d.department_name
FROM
   employees e,
   departments d
WHERE
   e.department_id = d.department_id ;

2.非等值聯結

非等值聯結是包含非等號運算子的聯結條件

SELECT
   e.last_name,
   e.salary,
   j.grade_level
FROM
   employees e,
   job_grades j
WHERE
   e.salary BETWEEN j.lowest_sal AND j.highest_sal;

3.外聯結

通過外聯結返回不直接匹配的記錄。

外聯結運算子只能出現在表示式的一側，即缺少資訊的那一側。他將從一個表中返回在另一個表中沒有直接匹配的行。

包含外聯結的條件不能用IN 運算子，也不能通過OR運算子連結到另一個條件。

SELECT
   e.last_name,
   e.department_id,
   d.department_name
FROM
   employees e,
   departments d
WHERE
   e.department_id(+) = d.department_id ;

4.自聯結

自己聯結自己的一種聯結形式

SELECT
   worker.last_name||'works for '||manager.last_name
FROM
   employees as worker,
   employees as manager
WHERE
   worker.manager_id = manager.employee_id;

5.交叉聯結：

返回兩個表的交叉乘積這與兩個表之間的笛卡爾乘積是相同的 CROSS JOIN

SELECT
   last_name,
   department_name
FROM
   employees
CROSS JOIN
   departments;

該語句的另外一種寫法就是不加任何條件的SELECT 語句

SELECT
   last_name,
   department_name
FROM
   employees,
   departments;

6.自然聯結：

NATURAL JOIN 子句是以兩個表中具有相同名稱的所有列為基礎。

它選擇兩個表中那些在所有匹配的列中值相等的行。

如果列具有相同的名稱但是資料型別不同，就會返回一個錯誤。

同時需要注意的是，選擇的欄位是兩個共有的，則不需要制定列名，否則會報錯

SELECT
   a.last_name,   department_id,   b.department_name
FROM
   employees a NATURAL JOIN departments b;

7.USING子句

如果幾個列具有相同的名稱，但是資料型別不匹配，則可以使用USING 子句來修改NATURAL JOIN子句以指定要用於等值聯結的列。

在多個列匹配時，使用USING子句只匹配一個列。

在引用列中不要使用表名或別名

NATURAL JOIN 和 USING 子句是互不相容的。

SELECT
   l.city,
   d.department_name
FROM
   locations l
JOIN
   department d
USING (location_id)
WHERE location_id = 1400;

對於使用Using限制只用一個相同列來關聯的，where條件當中出現的相同的列則必須限定為某一個表的列否則因產生歧義而丟擲錯誤

SELECT
   a.hire_date,
   b.department_name
FROM
   employees a
JOIN
   departments b
USING (manager_id)
WHERE a.department_id IN (20,30,100);

8.使用ON子句建立聯結

自然聯結的聯結條件基本上是具有相同名稱的所有列的等值聯結。

要制定任意條件或指定要聯結的列，可以使用ON子句。

聯結條件與其他搜尋條件分開。

ON子句使程式碼非常易於理解

兩表關聯

SELECT
   e.employee_id,
   e.last_name,
   e.department_id,
   d.department_id,
   d.location_id
FROM
   employees e
JOIN
   departments d
ON
   (e.department_id = d.department_id);

三表關聯（等值連線）

SELECT
   employee_id,
   city,
   department_name
FROM
   employees e
JOIN
   departments d
ON
   d.department_id = e.department_id
JOIN
   locations l
ON
   d.location_id = l.location_id;

相同結果的寫法

SELECT
   employee_id,
   city,
   department_name
FROM
   employees e,
   departments d,
   locations l
WHERE
   d.department_id = e.department_id
AND
   d.location_id = l.location_id;

9 INNER 與 OUTER 聯結

在SQL:99標準中，只返回匹配行的兩個表之間的聯結叫做：內聯結。

兩個表之間的聯結不但返回內聯結結果而且返回左（或右）表不匹配行的結果。

兩個表之間的聯結不但返回內聯結結果而且返回左聯結和右聯結不相匹配的結果，這樣的聯結就是完全外聯結

關於左/右外聯結的理解：

由於左右兩個表完全匹配的情況稱為內聯結，那麼左外聯結則可以理解為除了匹配的結果外，還將列出左表匹配以外的記錄。

右外聯結則是除了顯示兩表匹配的結果，還將顯示右表除匹配結果以外的記錄。

LEFT OUTER JOIN：

SELECT
   e.last_name,
   e.department_id,
   d.department_name
FROM
   employees e LEFT OUTER JOIN departments d
ON
   e.department_id = d.department_id;

與左外聯結相同的寫法

SELECT
   e.last_name,
   e.department_id,
   d.department_name
FROM
   employees e,
   departments d
WHERE
   e.department_id = d.department_id (+);

RIGHT OUTER JOIN：

SELECT
   e.last_name,
   e.department_id,
   d.department_name
FROM
   employees e RIGTH OUTER JOIN departments d
ON
   e.department_id = d.department_id;

相同的寫法

SELECT
   e.last_name,
   e.department_id,
   d.department_name
FROM
   employees e ,
   departments d
WHERE
   e.department_id(+) = d.department_id;

全外聯結：

SELECT
   e.last_name,
   e.department_id,
   d.department_name
FROM
   employees e
FULL OUTER JOIN
   departments d
ON (e.department_id = d.department_id) ;

全外關聯，oracle貌似沒有支援要達到全外關聯的效果需要用比較複雜的SQL來實現

select
   e.last_name,
     e.department_id,
     d.department_name
from
     employees e ,
     departments d
where
     e.department_id = d.department_id
union all
(select
     e.last_name,
     e.department_id,
     d.department_name
  from
     employees e ,
     departments d
  where
     e.department_id = d.department_id(+)
  minus
  select
     e.last_name,
     e.department_id,
     d.department_name
  from
     employees e ,
     departments d
  where
     e.department_id = d.department_id) --employees表中與department表不匹配的記錄
  union all
  (select
     e.last_name,
     e.department_id,
     d.department_name
  from
     employees e ,
     departments d
  where
     e.department_id(+) = d.department_id
  minus
  select
     e.last_name,
     e.department_id,
     d.department_name
  from
     employees e ,
     departments d
  where
     e.department_id = d.department_id) --department表中與employees表不匹配的記錄

>ANY 意味著大於最小值

=ANY 等同於 IN

>ALL 意味著大於最大值

NOT IN 運算子等同於 <>ALL

NOT 運算子可以和 IN ANY和ALL 運算子配合使用

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12216142/viewspace-680703/，如需轉載，請註明出處，否則將追究法律責任。