赞
踩
自连接:
能够在单独一条SELECT语句内多次引用同一个表。
- select prod_id,prod_name
- from products
- where vend_id = ( select vend_id
- from products
- where prod_id='DTNTR')
- select prod_id,prod_name
- from products p1,products p2
- where p1.vend_id = p2.vend_id
- and p2.prod_id='DTNTR'
自然连接:
- select c.*,o.order_num,o.order_date
- from customer c,orders o
- where c.cust_id=o.cust_id
- and prod_id='FB'
- select c.*,o.order_num,o.order_date
- from customer c,orders o
- on c.cust_id=o.cust_id
- where
- prod_id='FB'
左外连接:
- select c.*,o.order_num,o.order_date
- from customer
- LEFT OUTER JOIN orders
- on customer.cust_id=orders.cust_id
- where
- prod_id='FB'
右外连接:
- select c.*,o.order_num,o.order_date
- from customer
- RIGHT OUTER JOIN orders
- on customer.cust_id=orders.cust_id
- where
- prod_id='FB'
可以把左外连接转换成右外连接,只需要在FROM或where子句中点到表的顺序即可。
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。