內連接查詢

內連接定義

A表和B表能夠完全匹配的記錄查詢出來，稱為內連接。

等值連接

SQL92語法

select e.ename, d.dname from emp e, dept d where e.ename = d.dname and xxx;

SQL99語法（表連接條件和過濾分離）

select e.ename, d.dname from emp e inner join dept d on e.ename = d.dname where xxx;
select e.ename, d.dname from emp e join dept d on e.ename = d.dname where xxx;  // inner 可以省略

select
        e.ename, d.dname
from
        emp e, dept d;

非等值連接

SQL92語法

select e.ename, e.sal, s.grade from emp e, salgrade s where e.sal between s.losal and hisal;

SQL99語法（表連接條件和過濾分離）

select e.ename, e.sal, s.grade from emp e inner join salgrade s on e.sal between s.losal and hisal;
select e.ename, e.sal, s.grade from emp e join salgrade s on e.sal between s.losal and hisal;   // inner 省略

select
        e.sal, s.sal, s.grade
from
        emp e, salgrade s;

自連接

一張表看成兩張表，員工表 / 主管表

empno	ename	mgr
20	JACK	50
10	LUCY	20
30	MIKE	50
50	MERRY	20

SQL92語法

select a.ename empname, b.ename leadername from emp a, emp b where a.mgr = b.empno;

SQL99語法（表連接條件和過濾分離）

select a.ename empname, b.ename leadername from emp a inner join emp b on a.mgr = b.empno;
select a.ename empname, b.ename leadername from emp a join emp b on a.mgr = b.empno;   // inner 省略

select
        a.ename empname, b.ename leadername
from
        emp a inner join emp b on a.mgr = b.empno;

其他

注意 not in 不會排除 NULL。 注意 in 自動排除 NULL。

not in(100, 200, NULL, 300)

補充： case...when...then...when...then...else...end 使用在DQL語句中，類似 java switch...case...

select
        ename, sal, (case job when 'MANAGER' then sal*1.1 when 'SALESMAN' sal*1.5 else sal end) as newsal
from
        emp;