oracle中join in,oracle sql join 语法

Among several new features, Oracle9i has introduced the ANSI

standard join syntax. The new join syntax is not only SQL92

compliant, but it is elegant and makes the outer join syntax more

intuitive. The biggest advantage of the new join syntax is its

support for full outer joins, which is not directly feasible in

Oracle8i. In this article I discuss the full outer join feature of

Oracle9i, and provide some examples.

An outer join extends the result of an inner join by including rows

from one table (say Table A) that don't have corresponding rows in

another table (say Table B). An important thing to note here is

that the outer join operation will not include the rows from Table

B that don't have corresponding rows in Table A. In other words, an

outer join is unidirectional. But there are situations when you may

want a bidirectional outer join, i.e., you want to include all the

rows from A and B:

rows from the result of the inner join

rows from A that don't have corresponding rows in B

rows from B that don't have corresponding rows in A

Let's look at an example to understand this further. Consider the

following two tables:

SQL> desc part

Name Null? Type

----------------------------------------- --------

-----------------

PART_ID NOT NULL VARCHAR2(4)

SUPPLIER_ID VARCHAR2(4)

SQL> select * from part;

PART SUPP

---- ----

P1 S1

P2 S2

P3

P4

SQL> desc supplier

Name Null? Type

----------------------------------------- --------

-----------------

SUPPLIER_ID NOT NULL VARCHAR2(4)

SUPPLIER_NAME NOT NULL VARCHAR2(20)

SQL> select * from supplier;

SUPP SUPPLIER_NAME

---- --------------------

S1 Supplier#1

S2 Supplier#2

S3 Supplier#3

Notice above that there are two parts (P3 and P4) that don't have a

supplier yet. Also, there is a supplier (S3) who doesn't yet supply

any part.

Result of Inner Join

Let's say our company is consolidating all of the parts and the

suppliers for managing the inventory of its parts properly, and I

was asked to generate a report of all the parts and their

corresponding suppliers. So I performed a join of these two tables,

which gave the following result:

SQL> select p.part_id, s.supplier_name

2 from part p, supplier s

3 where p.supplier_id = s.supplier_id;

PART SUPPLIER_NAME

---- --------------------

P1 Supplier#1

P2 Supplier#2

The join shown above is an inner join, which results in just the

rows that have corresponding rows in both tables. Therefore, the

parts that don't have a supplier, or the suppliers that don't

supply any part are excluded from the result set.

Result of Outer Join

If we want all parts to be listed in the result set, irrespective

of whether they are supplied by any supplier or not, then we need

to perform an outer join.

SQL> select p.part_id, s.supplier_name

2 from part p, supplier s

3 where p.supplier_id = s.supplier_id (+);

PART SUPPLIER_NAME

---- --------------------

P1 Supplier#1

P2 Supplier#2

P3

P4

The outer join above lists all of the parts. For the parts that

don't have a corresponding supplier, null values are displayed for

the SUPPLIER_NAME column. However, not all the suppliers are

displayed. Since supplier S3 doesn't supply any parts, it gets

excluded from the result set of the above outer join. If we want

all the suppliers listed in the result set, irrespective of whether

they supply any part or not, we need to perform an outer join like

the following:

SQL> select p.part_id, s.supplier_name

2 from part p, supplier s

3 where p.supplier_id (+) = s.supplier_id;

PART SUPPLIER_NAME

---- --------------------

P1 Supplier#1

P2 Supplier#2

Supplier#3

The outer join above lists all the suppliers. For the suppliers

that don't supply any part, null values are displayed for the

PART_ID column. However, not all the parts are displayed. Since

parts P3 and P4 are not supplied by any suppliers, they get

excluded from the result set of the above outer join.

Full Outer Join

If we want all the parts (irrespective of whether they are supplied

by any supplier or not), and all the suppliers (irrespective of

whether they supply any part or not) listed in the same result set,

we have a problem. That's because the traditional outer join (using

the '+' operator) is unidirectional, and you can't put (+) on both

sides in the join condition. The following will result in an

error:

SQL> select p.part_id, s.supplier_name

2 from part p, supplier s

3 where p.supplier_id (+) = s.supplier_id (+);

where p.supplier_id (+) = s.supplier_id (+)

*

ERROR at line 3:

ORA-01468: a predicate may reference only one

outer-joined table

Up through Oracle8i, Oracle programmers have used a workaround to

circumvent this limitation. The workaround involves two outer join

queries combined by a UNION operator, as in the following

example:

SQL> select p.part_id, s.supplier_name

2 from part p, supplier s

3 where p.supplier_id = s.supplier_id (+)

4 union

5 select p.part_id, s.supplier_name

6 from part p, supplier s

7 where p.supplier_id (+) = s.supplier_id;

PART SUPPLIER_NAME

---- --------------------

P1 Supplier#1

P2 Supplier#2

P3

P4

Supplier#3

Notice the use of the UNION set operator to combine the results of

two separate outer join queries, to list all the parts and all the

suppliers in the same result set.

New Join Syntax

Oracle9i introduced the ANSI compliant join syntax. This new join

syntax uses the new keywords inner join, left outer join, right

outer join, and full outer join, instead of the (+) operator.

The inner join using this new join syntax will look

like:

SQL> select p.part_id, s.supplier_name

2 from part p inner join supplier s

3 on p.supplier_id = s.supplier_id;

PART SUPPLIER_NAME

---- --------------------

P1 Supplier#1

P2 Supplier#2

Remember, if we want to retain all the parts in the result set,

irrespective of whether any supplier supplies them or not, then we

need to perform an outer join. The corresponding outer join query

using the new syntax will be:

SQL> select p.part_id, s.supplier_name

2 from part p left outer join supplier s

3 on p.supplier_id = s.supplier_id;

PART SUPPLIER_NAME

---- --------------------

P1 Supplier#1

P2 Supplier#2

P4

P3

This is called a "left outer join" because all the rows from the

table on the left (PART) are retained in the result set. If we want

to retain all the suppliers in the result set, irrespective of

whether they supply any part or not, then we need to perform a

"right outer join". That would look like:

SQL> select p.part_id, s.supplier_name

2 from part p right outer join supplier s

3 on p.supplier_id = s.supplier_id;

PART SUPPLIER_NAME

---- --------------------

P1 Supplier#1

P2 Supplier#2

Supplier#3

However, the biggest advantage of the new join syntax is its

support for full outer joins. Introduction of the ANSI standard

join syntax in Oracle9i greatly simplifies the full outer join

query. We are no longer limited by unidirectional outer join, and

no longer need to use the UNION operation to perform the full outer

join. Oracle9i introduced the full outer join operation to carry

out such operations, as in the following

example:

SQL> select p.part_id, s.supplier_name

2 from part p full outer join supplier s

3 on p.supplier_id = s.supplier_id;

PART SUPPLIER_NAME

---- --------------------

P1 Supplier#1

P2 Supplier#2

P4

P3

Supplier#3

The above SQL statement is not only smaller in size, it is much

more elegant and intuitive as well. This ANSI join syntax is also

more efficient than the UNION method of achieving a full outer

join.