oracle连接多个表

连接多个表,Oracle SQL和PL/SQL编程,目标,创建一个笛卡尔连接 使用WHERE子句创建相等连接 使用JOIN关键字创建相等连接 使用WHERE子句创建不等连接 使用JOINON方法创建不等连接 创建自我连接 区分内部连接和外部连接 使用WHERE子句创建外部连接 使用OUTER关键字创建外部连接 使用集合运算符结合多个查询的结果 连接三个或更多表,笛卡尔连接,又称为“笛卡尔乘积”或“交叉连接”，第一表中的每一个数据与第二个表中的每一个记录配对 如果第一个表中有m条记录，第二个表中有n条记录，结果是m*n条记录,笛卡尔连接传统方法,查找每本书的出版社名称,SELECT title,name FROM books,publisher;,笛卡尔连接JOIN方法,Oracle 中，可以使用CROSS关键字与JOIN关键字一起使用用来创建笛卡尔连接,SELECT title,name FROM books CROSS JOIN publisher;,相等连接,实际工作中，最常用的连接类型是对一个公共列中存储了相同数据的两个（或多个）表创建的，这种连接被称为“相等连接”，也可以称为“同等连接”，“内部连接”或“简单连接”。 公共列是两个或更多表中存在相同数据的列，比如， boooks和publisher表都包含一个Pubid的公共列，这个公共列标识为每一个出版社指定的标识代码。,相等连接传统方法,传统方法是使用WHERE子句，等号用来指定对于在结果中连接和返回的行，每一个表中的Pubid列和内容必须完全相等。,SELECT title,name FROM books,publisher WHERE books.pubid = publisher.pubid;,注意：WHERE子句中的pubid列名称前面包括了对应得表名称。,相等连接传统方法,需要输出这个pubid时，必须在SELECT子句中指明哪个表的，以便引起多义性错误，也就是说连接查询的两个表中有相同列明的时候，要指明具体是哪个表的列要显示,SELECT title,books.pubid,name FROM publisher,books WHERE publisher.pubid = books.pubid AND publisher.pubid=4;,相等连接传统方法,查询成本低于15.00美元或者出版商id为1的任何图书书名，出版社编号，出版社名称，书的成本价,SELECT title,p.pubid,name,cost FROM publisher p,books b WHERE p.pubid=b.pubid AND (cost15 OR b.pubid=1);,用到了表别名，作用是为表临时指定一个不同的名称， 原因：表名过长不便于使用，可以使用表别名，还可以减少需要的内存,要记住一个规则：如果在FROM子句中指定了一个表别名，那么只要在这个SQL语句中引用这个表，就必须使用这个别名。,相等连接JOIN方法,可以使用三种JOIN关键字的方法来创建相等连接： NATURAL JOIN,JOINUSING和JOINON NATURAL JOIN关键字将在包括一个同等命名和定义的字段的两个表之间自动创建连接 使用USING子句可以根据在两个表中具有相同名称和定义的列创建连接 当USING子句中连接的表没有包括一个同等命名和定义的字段时，必须添加一个WHERE子句或者向JOIN关键字添加ON子句，以便制定这两个表之间的关系。,相等连接JOIN方法,列出books表中每一本书的书名以及对应的出版社ID和出版社名称,SELECT title,pubid,name FROM publisher NATURAL JOIN books;,注意：不需要指定两个表包含哪一个或哪些公共列，但暗示了至少存在一个具有相同名称的公共列,相等连接JOIN方法,JOINUSING方式 JOINON方式,SELECT title,pubid,name FROM books JOIN publisher USING (pubid);,SELECT title ,name FROM books b JOIN publisher p ON b.pubid = p.pubid;,如果不查询公共列时,SELECT title,b.pubid,name FROM books b JOIN publisher p ON b.pubid = p.pubid;,如果查询公共列，要指出具体出自哪个表,相等连接JOIN方法,随JOIN关键字使用USING和ON子句，其间存在的两个主要区别 只有当连接的表包括一个具有相同名称的公共列式才能使用USING子句，这不是ON子句所必须的 条件是在ON子句中指定的，这在USING中不允许，USING子句只能包含公共列的名称,练习1,创建一个列表，显示每本书的书名以及出版社办公室中你再次定购每本书时需要与之联系的人的姓名和电话号码。,不等连接,在不能使用等号连接相关列的时候，也就是表中不存在可以连接的相同行的时候，我们可以使用“不等连接” 不等连接允许你将一个范围的最小值存储在记录的一列，这个范围的最大值存储在另一列,不等连接传统方法,books表和promotion表之间的行不包含相等的值，所以你需要使用不等连接来确定客户将在促销活动中得到什么礼品。,SELECT * FROM promotion;,SELECT title,gift FROM books,promotion WHERE retail BETWEEN minretail AND maxretail;,不等连接JOIN方法,SELECT title,gift FROM books JOIN promotion ON retail BETWEEN minretail AND maxretail;,练习2,订购图书SHORTEST POEMS 的客户将收到什么样的礼品？,自我连接,一个表中的数据引用了同一个表中存储的其他数据,自我连接传统方法,customers 表referred列存储了介绍该新客户的个人的客户号,SELECT r.firstname,r.lastname,c.lastname referred FROM customers c,customers r WHERE c.referred = r.customer#;,自我连接JOIN方法,用JOINON方法,SELECT r.firstname,r.lastname,c.lastname referred FROM customers c JOIN customers r ON c.referred = r.customer#;,外部连接,在执行相等连接，不等连接，和自我连接时，只有当查询的每一个表都包括一个对应的纪录时才会返回一行，这些连接都可归为“内部连接”，因为只有当每一个表中都存在匹配数据时，记录才会在结果中列出，事实上是JOIN关键字默认的INNER关键字，INNER JOIN 当需要在连接查询的结果中包括存在于一个表中但是在另一个表中没有对应记录时，就需要外部连接，使用关键字OUTER JOIN。也可以在带有“外部连接运算符+”,外部连接传统方法,下面是customers 和orders表匹配的记录,SELECT lastname,firstname,order# FROM customers c,orders o WHERE c.customer# = o.customer# ORDER BY c.customer#;,不匹配会怎样？,外部连接传统方法,使用外部运算符（+）的外部连接,SELECT lastname,firstname,order# FROM customers c,orders o WHERE c.customer# = o.customer#(+) ORDER BY c.customer#;,结果中发现包含了NULL值，也就是说如果一个客户在customers表中，但最近没有下达订单，那么orders表中缺少对应的行或者是不完整的表（也就是说这个表缺少数据）。,因此，将外部连接运算符（+）放在了引用不完整的orders表的where子句中的连接条件部分之后。,外部连接传统方法,注意规则 外部连接运算符只能用于连接条件中的一个表，换句话说， 传统方式时，不能同时在两个表中创建NULL行 包括外部连接运算符的条件不能使用IN或OR运算符，因为这表明如果一行与其他表中的一行匹配或者满足其他某个指定的条件，那么它就显示在结果中。,外部连接JOIN方法,可以随JOIN关键字包括LEFT，RIGHT，或FULL来指定关联类型,SELECT lastname,firstname,order# FROM customers c LEFT OUTER JOIN orders o ON c.customer#=o.customer# ORDER BY c.customer#;,SELECT lastname,firstname,order# FROM customers c RIGHT OUTER JOIN orders o ON c.customer#=o.customer# ORDER BY c.customer#;,集合运算符,用来结合两个（或更多）SELECT语句的结 oracle中的集合预算符：UNION，UNION ALL，INTERSECT和MINUS UNION：返回两个查询的结果，如果存在重复记录，则将删除它们 UNION ALL：在结果中包含重复的记录 INTERSECT：只列出两个查询都返回的记录 MINUS：将从输出中删除第二个查询的结果,集合运算符,需要customers表中最近下了订单的所有客户的列表,SELECT customer# FROM customers UNION SELECT customer# FROM orders;,SELECT customer# FROM customers UNION ALL SELECT customer# FROM orders;,SELECT customer# FROM customers INTERSECT SELECT customer# FROM orders;,SELECT customer# FROM customers MINUS SELECT customer# FROM orders;,连接三个或更多表 连接三个或更多表传统方法,SELECT title,firstname,lastname FROM customers,orders,orderitems,books WHERE customers.customer# = orders. customer# AND orders.order# = orderitems.order# AND orderitems.isbn = books.isbn ORDER BY title;,连接三个或更多表 连接三个或更多表JOIN方法,JOIN ON NATURAL JOIN,SELECT title,firstname,lastname FROM customers JOIN orders ON customers.customer# = orders.customer# JOIN orderitems ON orders.order# = orderitems.order# JOIN books ON orderitems.isbn = books.isbn ORDER BY title;,SELECT title,firstname,lastname FROM customers NATURAL JOIN orders NATURAL JOIN orderitems NATURAL