表的联结:
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 last_name, department_name FROM employees, departments; |
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/,如需转载,请注明出处,否则将追究法律责任。
上一篇:
Fedora 14 正式版升级
![user_pic_default.png](http://blog.itpub.net/images/user_pic_default.png)
请登录后发表评论
登录
全部评论
<%=items[i].createtime%>
<%=items[i].content%>
<%if(items[i].items.items.length) { %>
<%for(var j=0;j
<%}%> <%if(items[i].items.total > 5) { %>
<%}%>
<%=items[i].items.items[j].createtime%>
<%=items[i].items.items[j].username%> 回复 <%=items[i].items.items[j].tousername%>: <%=items[i].items.items[j].content%>
还有<%=items[i].items.total-5%>条评论
) data-count=1 data-flag=true>点击查看
<%}%>
转载于:http://blog.itpub.net/12216142/viewspace-680703/