1：等值連接
迪卡爾集連接
select ename, a.deptno as a_deptno,b.deptno as b_deptno ,b.dname as 部門
from emp a, dept b
等值連接
select ename, a.deptno as a_deptno,b.deptno as b_deptno ,b.dname as 部門
from emp a, dept b
where a.deptno = b.deptno;
2：非等值連接，在emp表和salgrade表中查找員工的姓名，工資，等級，工資上線，工資下線
select ename as 姓名, sal as 工資, grade as 工資等級,losal as 工資上線，hisal as 工資下線
from emp, salgrade
where sal between losal and hisal;
3:外連
右外連
select e.ename, d.dname, e.deptno
from emp e,dept d
where e.deptno = d.deptno(+);
左外連與右外連相反
以hr登陸查詢表employees 和departments 表
select first_name as 姓名, department_name as 部門名稱, d.department_id as 部門編號
from employees e, departments d
where e.department_id= d.department_id(+) ;
4:自連：在同一個表中查詢每個員工及上司的工號和姓名
select a.empno as 員工編號, a.ename as 員工姓名, a.mgr as 上司的員工編號, b.ename as 上司姓名
from emp a, emp b
where a.mgr = b.empno;
5：sql99
交叉連接 cross join------相當于迪卡爾集
select e.ename,d.dname
from emp e cross join dept d;
自然連接 natural join------相當等值連接
select e.ename, d.dname
from emp e natural join dept d;
using子句-----使用同名列查詢
select e.ename, d.dname
from emp e join dept d
using (deptno);
on子句 ------當列名不同時用on子句
用on查詢兩張表
select e.ename, d.dname
from emp e join dept d
on e.deptno = d.deptno;
用on查詢多張表
select e.ename, d.dname
from emp e join dept d
on e.deptno = d.deptno
join 第三個表
on 列1 = 列2;
內連接（Inner join）-------內連接只返回滿足連接條件的數據
select employee_id, last_name, salary, department_id, department_name
from employees inner join departments using(department_id);
左外連
select employee_id, last_name, salary, department_id, department_name
from employees left join departments using(department_id);
右外連
select employee_id, last_name, salary, department_id, department_name
from employees right join departments using(department_id);
滿外連
select employee_id, last_name, salary, department_id, department_name
from employees full outer join departments using(department_id);
子查詢------------
查找出工資比scott高的人
select ename, sal from emp
where sal>
(select sal from emp where ename='SCOTT');
查找那些人和scott相同職位的人
select ename, job from emp
where job=
(select job from emp where ename='SCOTT')
and ename <> 'SCOTT';
any的用法 < any意味著小于最大、> any大于最小
select empno, ename, sal, job
from emp
where sal<any(select sal from emp where job='SALESMAN');
all的用法 < all：小于所有，即小于最小、> all：大于所有，即大于最大
select empno, ename, sal, job
from emp
where sal<all(select sal from emp where job='SALESMAN');