outer joins

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 the FROM 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 the FROM 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 the FROM 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的介绍了