ORACLE内连接（inner join），外连接（outer join），自然连接（nature join），等值连接，子查询，关联子查询

目录

ORACLE连接

内连接等效于等值连接

内连接

等值连接

外连接

左外连接

全连接

交叉连接

自然连接

ORACLE子查询

非关联子查询

关联子查询

标量子查询

---

ORACLE连接

以USER_1和USER_2为例子

内连接等效于等值连接

1.内连接和等值连接的效果是一样的，内连接oracle在处理的时候不会出现笛卡尔积现象，实际开发中建议选择内连接。

内连接

SELECT * FROM USER_1 INNER JOIN USER_2 ON USER_1.ID=USER_2.ID;-----内连接找出的是ID好相等的两张表的所有记录，必须加ON

SELECT * FROM USER_1  JOIN USER_2 ON USER_1.ID=USER_2.ID;

SELECT * FROM USER_1 INNER JOIN USER_2 USING(ID);----内连接加USING会将条件列去重

SELECT * FROM USER_1 INNER JOIN USER_2 USING(ID) WHERE USER_2.NIDE IS NOT NULL;

SELECT * FROM USER_1 INNER JOIN USER_2 ON USER_1.ID=USER_2.ID WHERE  USER_1.ID='2'AND  USER_2.NIDE IS NOT NULL;

SELECT * FROM USER_1  JOIN USER_2 ON USER_1.ID=USER_2.ID WHERE  USER_1.ID='2';---------------加了条件的内连接

等值连接

SELECT * FROM USER_1,USER_2 WHERE USER_1.ID=USER_2.ID;

SELECT * FROM USER_1 JOIN USER_2 ON USER_1.ID=USER_2.ID;

SELECT * FROM USER_1  JOIN USER_2 USING(ID);------等值连接加USING去重

外连接

外连接分为左外连接和右外连接，全连接

左连接也叫左外连接，右连接也叫右外连接。左连接显示左边表的全部数据，右连接显示右边表的全部数据。一下演示作外连接(LEFT JOIN/LEFT OUTER JOIN)。

左外连接

SELECT * FROM USER_1,USER_2 WHERE USER_1.ID=USER_2.ID(+);

SELECT * FROM USER_1 LEFT JOIN USER_2 ON USER_1.ID=USER_2.ID;

SELECT * FROM USER_1 LEFT OUTER JOIN USER_2 ON USER_1.ID=USER_2.ID;

SELECT * FROM USER_1 left OUTER JOIN USER_2 USING(ID);------外连接加USING去重

SELECT * FROM USER_1 left  JOIN USER_2 USING(ID);

SELECT * FROM USER_1 LEFT OUTER JOIN USER_2 ON USER_1.ID=USER_2.ID WHERE USER_2.ID IS NOT NULL;--------加了条件的外连接

全连接

select * from user_1 FULL OUTER JOIN USER_2 ON user_1.ID=user_2.ID；------全外连接是显示A表和B表的所有数据

select * from user_1 FULL OUTER JOIN USER_2 ON user_1.ID=user_2.ID WHERE USER_2.ID IS NOT NULL;-------------加了限制条件的全外连接

SELECT * FROM USER_1 FULL JOIN USER_2 USING(ID) ;---------------全外连接加USING后去重

交叉连接

交叉连接相当于笛卡尔积

SELECT * FROM USER_1,USER_2 ;

SELECT * FROM USER_1 CROSS JOIN USER_2;

自然连接

自然连接不用指定列，也不能使用on子句，自然连接会自动比较两张表的相同列，然后给列去重。

自然连接不能用USING

SELECT * FROM USER_1 NATURAL JOIN USER_2 ;

ORACLE子查询

子查询是一次性视图，在select语句执行完之后就会消失。

非关联子查询

SQL运行顺序：先运行子查询，再运行子查询外面的语句。子查询和主查询互不干扰。

单行子查询：

SELECT * FROM ISN WHERE ISN IN(SELECT ISN FROM MO_D WHERE MO='24099937');----多行子查询，子查询ISN返回多个值

SELECT * FROM ISN WHERE (ISN,MODEL) IN(SELECT ISN,MODEL FROM MO_D WHERE MO='24099937');----多行多列子查询

关联子查询

查找每个课程内大于对应课程平均成绩的学生。

SELECT 学号,课程号,成绩
FROM score AS s1
WHERE 成绩>(SELECT AVG(成绩)
            FROM score AS s2
            WHERE s1.课程号=s2.课程号);

关联子查询语句的执行顺序：

1、先执行外层查询，查找出score表里所有的学号、课程号、成绩，结果如下：

SELECT 学号,课程号,成绩
FROM score AS s1

2、上图中的第一条结果进入子查询，因为子查询里通过where语句关联了两个表，子查询得出课程号0001的平均成绩80。

(SELECT AVG(成绩)
            FROM score AS s2
            WHERE 课程号=0001)

3、将子查询得出的结果80返回外层查询where语句中进行比较，得出执行结果。

可以看出第一条记录不满足查询条件，查询结果中将不显示此条记录。

SELECT 学号,课程号,成绩
FROM score AS s1
WHERE 学号=0001 AND 课程号=001 AND 成绩>80;

4、score表中后面的每行数据进行同样的执行顺序，得出最终结果。

标量子查询

什么时候用标量子查询：当我们需要单一值的时候。

注意事项：不能返回多行结果。

标量子查询必须且只能返回一行一列的结果。返回单一的值。所以可以和比较运算符一起使用。

标量子查询的运用

SELECT 学号,成绩
FROM score
WHERE 成绩>(SELECT AVG(成绩)
            FROM score);