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