联结表
数据库设计:相同数据不应出现多次.
#等值联结
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语法.
- 使用明确联结语法,能够确保不会忘记连接条件
- 使用内部联结的性能或许会好一点
- 缺点:语法没有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';