表的联结：

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博客 ” ，链接：https://blog.itpub.net/12216142/viewspace-680703/ ，如需转载，请注明出处，否则将追究法律责任。

ITPub博客

表的联结、运算符学习笔记

最新文章