数据库基础08——多表查询
1、等值联结(内联结)
select * from A,B where 条件;--隐式内连接
select * from A inner join B on 条件;-- 显示内连接
----------------还有一种交叉联结,基本不会用,得到的是笛卡尔积,两个表的乘积
select * from A,B; --所以,where 条件的重要性
ANSI SQL 规范首选 INNER JOIN 语法,之前使用的是简单的等值语法。其实, SQL 语言纯正论者是用鄙视的眼光看待简单语法的。这就是说, DBMS 的确支持简单格式和标准格式,我建议你要理解这两种格式,具体使用就看你用哪个更顺手了。
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;
--------------------------------------------
SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products
ON Vendors.vend_id = Products.vend_id;
-- 这两种方式的结果一样。
联结多个表
SELECT prod_name, vend_name, prod_price, quantity
FROM OrderItems, Products, Vendors
WHERE Products.vend_id = Vendors.vend_id
AND OrderItems.prod_id = Products.prod_id
AND order_num = 20007;
--都一样,只不过是表多了。
不要联结不必要的表。联结的表越多,性能下降越厉害
2、使用表别名
- 缩短 SQL 语句;
- 允许在一条 SELECT 语句中多次使用相同的表
SELECT cust_name, cust_contact
FROM Customers AS C, Orders AS O, OrderItems AS OI
WHERE C.cust_id = O.cust_id
AND OI.order_num = O.order_num
AND prod_id = 'RGAN01';
--Oracle 没有as
3、自联结
------查询出Jim Jones 所在公司的所有员工
SELECT cust_id, cust_name, cust_contact
FROM Customers
WHERE cust_name = (SELECT cust_name
FROM Customers
WHERE cust_contact = 'Jim Jones');
--当然还有另一种方法,使用自联结
SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM Customers AS c1, Customers AS c2 --使用别名 --如果是oracle 去掉as
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim Jones';
提示:用自联结而不用子查询
自联结通常作为外部语句,用来替代从相同表中检索数据的使用子查询语句。虽然最终的结果是相同的,但许多 DBMS 处理联结远比处理子查询快得多。
4、自然联结
无论何时对表进行联结,应该至少有一个列出现在不止一个表中(被联结的列)。标准的联结(前一章中介绍的内部联结)返回所有数据,甚至相同的列多次出现。 自然联结排除多次出现,使每个列只返回一次。
SELECT C.*, O.order_num, O.order_date,
OI.prod_id, OI.quantity, OI.item_price
FROM Customers AS C, Orders AS O, OrderItems AS OI
WHERE C.cust_id = O.cust_id
AND OI.order_num = O.order_num
AND prod_id = 'RGAN01';
------- c.* 外加不重复的列
--即:对表使用通配符(SELECT *),对所有其他表的列使用明确的子集来完成的
事实上,迄今为止我们建立的每个内部联结都是自然联结,很可能我们永远都不会用到不是自然联结的内部联结。
5、外联结
--左外连接 left outer join on
select * from A left outer join B on 条件;
--右外连接: right outer join on
select * from A right outer join B on 条件;
--左联结: 左边的表这里是Customers选中所有行,另一个表如果不足就用空行补足。
SELECT Customers.cust_id, Orders.order_num
FROM Customers INNER JOIN Orders
ON Customers.cust_id = Orders.cust_id;
--右联结: 右边的表,Orders选中所有行
SELECT Customers.cust_id, Orders.order_num
FROM Customers RIGHT OUTER JOIN Orders
ON Orders.cust_id = Customers.cust_id;
-- 查询要保证查询的不要有重复列即要用自然联结,自己处理。
--在我理解就是, 一左一右,左边的选中所有行,然后一一和右边表比对。 左联就是自己放到左边,右联自己放到右边。可以这么理解。 其实这两种是可以互换的换个顺序和条件就行,哪个方便用哪个。
6、全外联结(了解)
它检索两个表中的所有行并关联那些可以关联的行。与左外联结或右外联结包含一个表的不关联的行不同,全外联结包含两个表的不关联的行
SELECT Customers.cust_id, Orders.order_num
FROM Orders FULL OUTER JOIN Customers
ON Orders.cust_id = Customers.cust_id;
--Access、 MariaDB、 MySQL、 Open Office Base 和 SQLite 不支持 FULL OUTER JOIN 语法。
-- 那么就是Oracle 支持
注意事项:
- 注意所使用的联结类型。一般我们使用内联结,但使用外联结也有效。
- 关于确切的联结语法,应该查看具体的文档,看相应的 DBMS 支持何种语法
- 保证使用正确的联结条件(不管采用哪种语法),否则会返回不正确的数据。
- 应该总是提供联结条件,否则会得出笛卡儿积。
- 在一个联结中可以包含多个表,甚至可以对每个联结采用不同的联结类型。虽然这样做是合法的,一般也很有用,但应该在一起测试它们前分别测试每个联结。