1.内连接

表名 INNER JOIN 表名 ON 条件
等价于：
FROM 表名,表名
WHERE 条件

SELECT p.name,pt.name,pt.product_type_id
FROM products p INNER JOIN product_types pt ON p.product_type_id = pt.product_type_id
ORDER BY p.name;

表名 INNER JOIN 表名 USING (属性)
注意：在SELECT中的属性不能加前缀，如pt.product_type_id会报错

SELECT p.name,product_type_id
FROM products p INNER JOIN product_types pt USING (product_type_id)
ORDER BY p.name;

多于两个表的内连接
SELECT c.first_name,c.last_name,p.name AS PRODUCT,pt.name AS TYPE
FROM customers c,products p,product_types pt,purchases pr
WHERE c.customer_id = pr.customer_id
AND pr.product_id = p.product_id
AND p.product_type_id = pt.product_type_id
ORDER BY p.name;
用INNER JOIN USING重写
SELECT c.first_name,pt.name AS TYPE
FROM customers c INNER JOIN purchases pr
USING (customer_id)
INNER JOIN products p
USING (product_id)
INNER JOIN product_types pt
USING (product_type_id)
ORDER BY p.name;

2. 外连接

sql/92执行外连接
语法：FROM table1 {LEFT | RIGHT | full} OUTER JOIN table2
left、right分别表左、右连接
full表全连接，这是+操作符无法做到的

SELECT p.name,pt.name
FROM products p,product_types pt
WHERE p.product_type_id = pt.product_type_id (+)
ORDER BY p.name;
上面的左连接改成sql/92形式则为：
SELECT p.name,pt.name
FROM products p LEFT OUTER JOIN product_types pt USING (product_type_id)
ORDER BY p.name;

SELECT p.name,product_types pt
WHERE p.product_type_id = pt.product_type_id (+)
ORDER BY p.name;
上面的右连接改成sql/92形式则为：
SELECT p.name,pt.name
FROM products p RIGHT OUTER JOIN product_types pt USING (product_type_id)
ORDER BY p.name;

3.自连接

使用sql/92执行自连接
与内连接一样，只是table1与table2是同一表。

4.交叉连接

使用sql/92进行交叉连接：
语法:FROM talbe1 CROSS JOIN table2

SELECT *
FROM products,product_types;
可以改成：
SELECT *
FROM products CROSS JOIN product_types;

转载：
原文地址：

http://www.cnblogs.com/visayafan/archive/2011/10/20/2219612.html