oracle-高级查询（连接查询）

缺乏、安全感 2021-09-23 06:38 618阅读 0赞

*  使用=实现多个表的简单连接
 *  使用表的别名
 *  使用inner join实现多个表的内连接
 *  (1)等值连接
 *  (2)不等值连接
 *  (3)自然连接
 *  (4)使用using关键字简化连接
 *  使用outer join实现多个表的外连接
 *  使用cross join实现交叉连接
 *  使用union操作符获取两个结果集的并集
 *  使用intersect操作符获取两个结果集的交集（相同的部分）
 *  使用minus操作符获取两个结果集的差集

**一：使用=实现多个表的简单连接**

![20190903135422251.png][]表a

![20190903135438155.png][]表a\_sex

//使用=实现多个表的简单连接,结果是笛卡尔积（两个表行数的积）
    select a_bm.id,a_bm.name,a_sex_bm.name from a a_bm,a_sex a_sex_bm;

![2019090313552925.png][]

//在笛卡尔积所生成的表中包含了大量冗余信息，可以使用where子句限定检索条件，使用=
    select a_bm.id,a_bm.name,a_sex_bm.name from a a_bm,a_sex a_sex_bm where a_bm.id=a_sex_bm.id;

![20190903140100134.png][]

**二：使用表的别名**

//使用表的别名
    //注意：为表指定了别名，表的实际名称也就被覆盖
    //注意：from子句应当包括所有的表名
    //注意：应该使用where子句定义一个连接条件，避免笛卡尔积

**三：使用inner join实现多个表的内连接**

//使用inner join实现多个表的内连接
    //join_type:连接类型（只使用join，默认是内连接）
    from join_table1 join_type join_table2 [on (join_condition)]
    //join_condition:连接条件，由被连接表中的列和比较运算符，逻辑运算符等构成
    [join_type...on join_condition,...]

（1）等值连接

//在连接条件中使用=
    select a_bm.id,a_bm.name from a a_bm 
    inner join a_sex a_sex_bm 
    on a_bm.id=a_sex_bm.id 
    where a_sex_bm.id='1'

（2）不等值连接

//不等连接
    //使用除=外的比较运算符，比如>,<,<>(不等于)，!=(不等于)，like,in，between等
    //假如a_sex表中有一个列是min一个是max
    select a_bm.id,a_bm.name from a a_bm 
    inner join a_sex a_sex_bm 
    on a_bm.id between a_sex_bm.min and a_sex_bm.max;

（3）自然连接

![20190903144322361.png][]表a

![20190903135438155.png][]表a\_sex

//自然连接（natural join）
    //自然连接是在两个表中寻找列名和数据类型都相同的字段，通过相同的字段将两个表连接在一起，并返回所有符合条件的结果
    //使用这个不需要指定连接条件
    //注意：两个表的列名和类型都相同的字段不能使用表名或别名
    //注意：如果是列名相同，类型不同，会报错
    //使用场景：主键和外键使用相同类型和名字
    
    //如下就是id和name都是列名和类型相同，然后就有a.id=a_sex.id and a.name=a_sex.name条件
    select id,name from a a_bm 
    natural join a_sex a_sex_bm

![20190903145206426.png][]

（4）使用using关键字简化连接

//使用using关键字简化连接
    //满足两个条件才可以使用：
    //(1)查询必须是等值查询
    //(2)等值查询中的列必须具有相同的名称和数据类型
    //注意：使用表a和表a_sex中的id列和name列时，在using和select中id和name不能有别名或表名
    select id,name from a a_bm 
    inner join a_sex a_sex_bm 
    using (id,name)
    等价于
    select a_bm.id,a_bm.name from a a_bm 
    inner join a_sex a_sex_bm 
    on a_bm.id=a_sex_bm.id and a_bm.name=a_sex_bm.name;

//对多个表进行检索
    select ... from table1 inner join table2 using (column1)
    inner join table3 using (column2);
    //等价于
    select ... from table1,table2,table3
    where table1.column1=table2.column1
    and table2.column2=table3.column2;

**四：使用outer join实现多个表的外连接**

[https://blog.csdn.net/weixin\_42717928/article/details/100521789][https_blog.csdn.net_weixin_42717928_article_details_100521789]

**五：使用cross join实现交叉连接**

![20190903164126296.png][]表a\_sex

![20190903164153811.png][]表a

//得到的结果是两张表的各行数据的所有组合，即笛卡尔积
    //不需要on限定连接条件
    select a_bm.id,a_bm.name,a_sex_bm.name from a a_bm cross join a_sex a_sex_bm 
    where a_bm.id>4

![2019090316431250.png][]

**六：使用union操作符获取两个结果集的并集**

//union是集合操作符，union[all]:指定all包括重复行，不指定则不包括
    //要注意select要有相同的列或者表达式
    select id,name from a where id>4
    union all
    select id,name from a where id<2
    order by id asc;

![20190903165132642.png][]

**七：使用intersect操作符获取两个结果集的交集（相同的部分）**

//使用intersect操作符获取两个结果集的交集（相同的部分）
    //intersect是集合操作符
    select id,name from a where id>2
    intersect
    select id,name from a where id<4
    order by id asc;

![20190903165722935.png][]

**八：使用minus操作符获取两个结果集的差集**

//使用minus操作符获取两个结果集的差集
    //minus是集合操作符
    //注意：存在的元素只在前一个集合，不在后一个集合

select id,name from a where id>2
    minus
    select id,name from a where id<4
    order by id asc;

![20190903165937825.png][]

select id,name from a where id<4
    minus
    select id,name from a where id>2
    order by id asc;

![20190903170001719.png][]

附加：

//组合
    select id,name from a where id>2
    minus
    select id,name from a where id<4
    union
    select id,name from a where id=3
    order by id asc;

![20190903170141146.png][]

[20190903135422251.png]: /images/20210923/ad774cde57904954b9e670215a636ac6.png
[20190903135438155.png]: /images/20210923/b28f595fa2d3434d9b569ac2dcc3569a.png
[2019090313552925.png]: /images/20210923/952c7e3323c748fdb412a0dfaab488c7.png
[20190903140100134.png]: /images/20210923/654051760971478f910ab3ccc35dddf0.png
[20190903144322361.png]: /images/20210923/d5f8d2d1854142d9abbb7f60cb2aa780.png
[20190903145206426.png]: /images/20210923/4305741a32ff48e3baba949240b31d95.png
[https_blog.csdn.net_weixin_42717928_article_details_100521789]: https://blog.csdn.net/weixin_42717928/article/details/100521789
[20190903164126296.png]: /images/20210923/6d97dbee8a584513a7170689c269ca66.png
[20190903164153811.png]: /images/20210923/b8074ddbeeeb4ff08a04066c163b1d82.png
[2019090316431250.png]: /images/20210923/3e7eee304d124dbaa55442ac950d3b57.png
[20190903165132642.png]: /images/20210923/dac814eec38c406588496e911975b0b6.png
[20190903165722935.png]: /images/20210923/958b6391df844a6b90eeceb1893fbabe.png
[20190903165937825.png]: /images/20210923/4c333adc807c4f5fa5ba035e57df17e3.png
[20190903170001719.png]: /images/20210923/edde8256d15f4cfba07ff588fce064a2.png
[20190903170141146.png]: /images/20210923/3dd7823653a84edf85297235a4d08563.png