联结表学习

联结表

数据库设计:相同数据不应出现多次.

#等值联结
select vend_name,prod_name,prod_price
from vendors,products
where vendors.vend_id = products.vend_id
order by vend_name,prod_name;

联结实际上做的事是将第一张表的每一行与第二张进行匹配,若不加where则相当于两张表做笛卡尔积.

#内部联结
select vend_name,prod_name,prod_price
from vendors inner join products
on vendors.vend_id = products.vend_id;

等值联结相当于内部联结(inner join)

ANSI SQL规范首选INNER JOIN语法.

1. 使用明确联结语法,能够确保不会忘记连接条件
2. 使用内部联结的性能或许会好一点
3. 缺点:语法没有where简单

数据库对表进行联结处理非常耗费资源,应该仔细使用(就像spark里面的笛卡尔积算子一样..慎用)

#联结多个表
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 = 20005;

#三层嵌套子查询
select cust_name,cust_contact
from customers
where cust_id in(
	select cust_id
	from orders
	where order_num in(
		select order_num
		from orderitems
		where prod_id ='TNT2'));

#等价联结实现
select cust_name,cust_contact
from customers,orders,orderitems
where customers.cust_id = orders.cust_id
	and orders.order_num = orderitems.order_num
	and prod_id = 'TNT2';