连接查询
连接是关系数据库模型的主要特点。当两个或多个表中存在相同意义的字段时,便可以通过这些字段对不同的表进行连接查询。
因为fruits表和suppliers表中有相同的字段s_id,因此在比较的时候需要完全限定表名(格式为“ 表名.列名” 【中间是点号】),如果只给出s_id,Oracle将不知道指的是哪一个。并返回错误信息
select suppliers.id,s_name,f_name,f_price from fruits,suppliers where fruits.id=suppliers.id; (两个表含有相同的s_id,所以s_id需要使用 表名.列名 的形式,如果为一个表中独有的字段,如s_name,f_name,f_price字段,则不需要,直接写字段名就行)
自连接查询
如果在一个连接查询中,涉及的两个表都是同一个表,这种查询称为自连接查询。自连接查询是一种特殊的内连接。
--查询f_id=a1的水果供应商提供的其它水果 select f1.f_id,f1.f_name from fruits f1,fruits f2 where f1.sid=f2.s_id and f2.f_id='a1'; --where f1.sid=f2.s_id and f2.f_id='a1'可以得出s_id=101 下面语句与自连接效果相同 select f_id,f_name from fruits where s_id=(select s_id from fruits where f_id='a1'); 或者 select f_id,f_name from fruits where s_id in (select s_id from fruits where f_id='a1');
外连接(左(外)连接、右(外)连接、全(外)连接)
1、左连接 LEFT JOIN
左连接的结果包括 left outer join (其中outer可以省略不写)子句中指定的左表的所有行,而不仅仅是连接列所匹配的行。如果左表的某行在表中没有匹配行,则在相关的结果行中,右表的所有选择列均为空值。
--在customers表和orders表中,查询所有客户,包括没有订单的客户(left outer join中outer可以省略不写,) select customers.c_id,orders.o_num from customers left outer join orders on customers.c_id=orders.c_id; --其中customers.c_id=10002没有对应的订单,所以为null (左表) (右表)
2、右连接 RIGHT JOIN
右连接是左连接的反向连接,将返回右表的所有行,如果右表的某行在左表中没有匹配的行,左表将返回空值。
--在customers表和orders表中,查询所有订单,包括没有客户的订单 select order.c_id,customers.c_name from customers right outer join orders on customers.c_id=orders.c_id; --其中orders表中c_id=10005没有对应的客户,所以为null
带ANY、SOME、all、exists、in关键字的子查询
1、any、some关键字是同义词,表示满足其中任意条件
2、all关键字与any和some不同,使用all时需要同时满足所有内层查询的条件。
--tbl1中num1列的某个值比tbl2中num2列的任意一个数大就行 select num1 from tbl1 where num1>any(select num2 from tabl2); --tbl1中num1的某个值比num2列的所有值都大 select num1 from tbl1 where num1>all(select num2 from tabl2);
3、exists关键字后面的参数是一个任意的子查询,系统对子查询进行运算以判断它是否返回行,如果至少返回一行,那么exists的结果为true,此时外层查询语句将进行查询;
如果子查询没有返回任何行,那么exists返回的结果是false,此时外层语句将不进行查询
--查询suppliers表中是否存在s_id=107的供应商,如果存在,则查询fruits表中的记录 select * from fruits where exists (select s_name from suppliers where s_id=107); --查询suppliers表中是否存在s_id=107的供应商,如果存在,则查询fruits表中f_price>10.2的记录 select * from fruits where f_price>10.2 and exists (select s_name from suppliers where s_id=107);
not exists与exists使用方法相同,返回的结果相反
exists和not exists的结果只取决于是否会返回行,而不取决于这些行的内容,所以这个子查询输入列表通常是无关紧要的
执行子查询时,Oracle实际上执行了两个操作过程,即先执行内层子查询,再执行外层查询,内层子查询的结果作为外部查询的比较条件
4、IN
--在orderitems表中查询f_id为c0的订单号,并根据订单号查询orders表具有订单号的客户c_id select c_id from orders where o_num in (select o_num from orderitems where f_id='c0');