Outer Joins
An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.
-
To write a query that performs an outer join of tables A and B and returns all rows from A (aleft outer join), use the
LEFT
[OUTER
]JOIN
syntax in theFROM
clause, or apply the outer join operator (+) to all columns of B in the join condition in theWHERE
clause. For all rows in A that have no matching rows in B, Oracle Database returns null for any select list expressions containing columns of B. -
To write a query that performs an outer join of tables A and B and returns all rows from B (aright outer join), use the
RIGHT
[OUTER
]JOIN
syntax in theFROM
clause, or apply the outer join operator (+) to all columns of A in the join condition in theWHERE
clause. For all rows in B that have no matching rows in A, Oracle returns null for any select list expressions containing columns of A. -
To write a query that performs an outer join and returns all rows from A and B, extended with nulls if they do not satisfy the join condition (afull outer join), use the
FULL
[OUTER
]JOIN
syntax in theFROM
clause.
You cannot compare a column with a subquery in the WHERE
clause of any outer join, regardless which form you specify.
Using Outer Joins: Examples The following example shows how a partitioned outer join fills data gaps in rows to facilitate analytic function specification and reliable report formatting. The example first creates a small data table to be used in the join:
SELECT d.department_id, e.last_name FROM departments d LEFT OUTER JOIN employees e ON d.department_id = e.department_id ORDER BY d.department_id, e.last_name;
Users familiar with the traditional Oracle Database outer joins syntax will recognize the same query in this form:
SELECT d.department_id, e.last_name FROM departments d, employees e WHERE d.department_id = e.department_id(+) ORDER BY d.department_id, e.last_name;
我打算试试例子,但是scott没有departments这些表,于是查到
About Sample Schema HR
The HR
schema is a sample schema that can be installed as part of Oracle Database. This schema contains information about employees—their departments, locations, work histories, and related information. Like all schemas, theHR
schema has tables, views, indexes, procedures, functions, and other attributes of a database schema.
The examples and tutorials in this document use the HR
schema.
参考http://docs.oracle.com/cd/E11882_01/appdev.112/e10766/tdddg_intro.htm#TDDDG12400
以及如何解锁HR
ALTER USER HR ACCOUNT UNLOCK IDENTIFIED BY password;
参考:http://docs.oracle.com/cd/E11882_01/appdev.112/e10766/tdddg_connecting.htm#BABFHHCJ
这样通过例子就更快更好的理解上面outer joins的介绍了