Student: | Book: | |||
ID | Name | SID | Book | |
1 | feng | 1 | abc | |
2 | yong | null | def | |
[Inner] Join | ||||
ID | Name | SID | Book | |
1 | feng | 1 | abc | |
Left [Outer] Join: 左表所有行, 右表没有匹配行则均为null | ||||
ID | Name | SID | Book | |
1 | feng | 1 | abc | |
2 | yong | null | null | |
Right [Outer] Join | ||||
ID | Name | SID | Book | |
1 | feng | 1 | abc | |
null | null | null | def | |
Full [Outer] Join | ||||
ID | Name | SID | Book | |
1 | feng | 1 | abc | |
2 | yong | null | null | |
null | null | null | def | |
CROSS JOIN | ||||
ID | Name | SID | Book | |
1 | feng | 1 | abc | |
1 | feng | null | def | |
2 | yong | 1 | abc | |
2 | yong | null | def |
现在有三张表
a表
id goodsid 数量
1 1 12
2 2 13
b表
id name unitid
1 材料1 1
2 材料2 2
c表
unitid name
2 吨
3 kg
如果采用select b.name,c.name,a.数量 from a,b,c where a.goodsid = b.id and b.unitid=c.unitid 就无法检索出材料一,
此时应该用:
select b.name,c.name,a.数量
from a left outer join b on a.goodsid = b.id
left outer join c on b.unitid=c.unitid
子查询例子:
1、单行子查询
select ename,deptno,sal
from emp
where deptno=(select deptno from dept where loc='NEW YORK');
2、多行子查询
SELECT ename,job,sal
FROM EMP
WHERE deptno in ( SELECT deptno FROM dept WHERE dname LIKE 'A%');
3、多列子查询
SELECT deptno,ename,job,sal
FROM EMP
WHERE (deptno,sal) IN (SELECT deptno,MAX(sal) FROM EMP GROUP BY deptno);
4、内联视图子查询
(1)SELECT ename,job,sal,rownum
FROM (SELECT ename,job,sal FROM EMP ORDER BY sal);
(2)SELECT ename,job,sal,rownum
FROM ( SELECT ename,job,sal FROM EMP ORDER BY sal)
WHERE rownum<=5;
5、在HAVING子句中使用子查询
SELECT deptno,job,AVG(sal) FROM EMP GROUP BY deptno,job HAVING AVG(sal)>(SELECT sal FROM EMP WHERE ename='MARTIN');
INNER JOIN 查询:
FROM (((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON Member.字段号=表4.字段号) INNER JOIN 表X ON Member.字段号=表X.字段号
Tips:
如下两段代码等价
UNION操作:
UNION 指令的目的是将两个 SQL 语句的结果合并起来。从这个角度来看, UNION 跟 JOIN 有些许类似,因为这两个指令都可以由多个表格中撷取资料。 UNION 的一个限制是两个 SQL 语句所产生的栏位需要是同样的资料种类。另外,当我们用 UNION这个指令时,我们只会看到不同的资料值 (类似 Select DISTINCT)。 union只是将两个结果联结起来一起显示,并不是联结两个表………… 请注意,UNION 内部的 SELECT 语句必须拥有相同数量的列,UNION 的语法如下:
[SQL 语句 1]
UNION
[SQL 语句 2]