【Oracle】SQL/92 执行多个表的连接

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, pt.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, c.last_name, p.name AS PRODUCT, 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, 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 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;