Oracle查询的连接分三种:
1. 内连接(自然连接)
2. 外连接
2.1左外连接2.2右外连接
2.3完全外连接
3. 自连接
- 内连接
–连接的多表必须有公共列(相等连接)
–通过INNER JOIN语法实现
–语法:
SELECT tab_name1.col_name, tab_name2.col_name
FROM tab_name1 INNER JOIN tab_name2
ON tab_name1.col = tab_name2.col
其中col是两表的公共列
其效果等同于:
SELECT tab_name1.col_name, tab_name2.col_name
FROM tab_name1, tab_name2
WHERE tab_name1.col = tab_name2.col
2. 外连接
2.1 左外连接(LEFT JOIN)
–返回满足连接条件的所有行,而且还会返回不满足连接条件的连接操作符的左边表的其他行,即左边的表会全部显示出来
–语法:
Select d.dname, e.ename
from dept d left join emp e
on d.deptno = e.deptno and d.deptno = 10;
或这样写:
select d.dname, e.ename
from dept d ,emp e
where d.deptno = e.deptno(+) and d.deptno = 10;
结果如下:
2.2 右外连接(RIGHT JOIN)
–返回满足连接条件的所有行,而且还会返回不满足连接条件的连接操作符的右边表的其他行,即右边的表会全部显示出来
–语法:
Select d.dname, e.ename
from dept d right join emp e
on d.deptno = e.deptno and d.deptno = 10;
或这样写:
select d.dname, e.ename
from dept d, emp e
where d.deptno(+) = e.deptno and d.deptno = 10;
结果如下:
2.3 完全外连接(FULL JOIN)
–不仅返回满足连接条件的所有行,而且还会返回不满足连接条件的所有行
–语法:
select d.dname, e.ename
from dept d full join emp e
on d.deptno = e.deptno and d.deptno = 10;
3. 自连接
–同一张表之间的连接查询
–主要用于在参照表上显示上下级关系或层次关系
–语法:
select t1.col_name, t2.col_name
from table1 t1 inner join table1 t2
on t1.col_name = t2.col_name
(*^__^*) 嘻嘻……学习进步~
FROM tab_name1 INNER JOIN tab_name2
ON tab_name1.col = tab_name2.col
其中col是两表的公共列
其效果等同于:
SELECT tab_name1.col_name, tab_name2.col_nameFROM tab_name1, tab_name2
WHERE tab_name1.col = tab_name2.col
2. 外连接
2.1 左外连接(LEFT JOIN)
–返回满足连接条件的所有行,而且还会返回不满足连接条件的连接操作符的左边表的其他行,即左边的表会全部显示出来
–语法:
from dept d left join emp e
on d.deptno = e.deptno and d.deptno = 10;
select d.dname, e.ename
from dept d ,emp e
where d.deptno = e.deptno(+) and d.deptno = 10;
2.2 右外连接(RIGHT JOIN)
–返回满足连接条件的所有行,而且还会返回不满足连接条件的连接操作符的右边表的其他行,即右边的表会全部显示出来
–语法:
from dept d right join emp e
on d.deptno = e.deptno and d.deptno = 10;
from dept d, emp e
where d.deptno(+) = e.deptno and d.deptno = 10;
2.3 完全外连接(FULL JOIN)
–不仅返回满足连接条件的所有行,而且还会返回不满足连接条件的所有行
–语法:
from dept d full join emp e
on d.deptno = e.deptno and d.deptno = 10;
3. 自连接
–同一张表之间的连接查询
–主要用于在参照表上显示上下级关系或层次关系
–语法:
from table1 t1 inner join table1 t2