表连接的几种方式

 --自连接：

能够在单独一条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子句中点到表的顺序即可。