Re: OCP Natural Join Question

It would seem that in this question, the "natural join" is treated as a category - as opposed to the "NATURAL JOIN" clause. ORACLE puts the simple "JOIN" in the category of natural join - refer to Metalink Note:137286.1. You use "USING" with "JOIN". The key words "NATURAL" and "USING" are indeed mutually exclusive. If we could all understand what they are asking....

Here is an example:

SQL> select c1, c2 from test1 natural join test2;

C1 C2
-- ----------

b           2
b           2
e           1
e           1

SQL> select c1, test1.c2 from test1 join test2 using (c1);

C1 C2
-- ----------

b           2
b           2
c           1
c           1
e           1
e           1
G           7
G           7
G           7

9 rows selected.

SQL>

• Jusung Yang

"Buck Turgidson" <jc_va_at_hotmail.com> wrote in message news:<Mqnfa.69735$_F2.5820052_at_news1.east.cox.net>...
> According to the answer key, B, D, and F are correct. I am trying to figure out
> how ON or USING could be used with a natural join to limit the join conditions.
> Neither of the 2 statements shown below, where I try and limit the NJ columns,
> According to the answer key, B, D, and F are correct. I am trying to figure out
> how ON or USING could be used with a natural join to limit the join conditions.
> Neither of the 2 statements shown below are valid.
>
> I'd appreciate some input.
>
>
>
> --
> Oracle9i provides an ON clause and a USING clause to assist in defining join
> conditions.
>
> Which three statements regarding the use of these clauses are true? (Choose
> three.)
>
> A) A USING clause can be used with LOBs.
> B) Subqueries can be used in the join predicate of an ON clause.
> C) An ON clause must be specified when performing a natural join.
> D) Multiple ON clauses can be used and are evaluated in the order listed.
> E) A USING clause can implement either an equijoin or a non-equijoin condition.
> F) A USING clause can be used with a natural join to limit the columns for the
> join condition.
> --
>
>
> create table pay_check(empno number, pay_date date,net_amount number);
>
> create table pay_earnings(empno number, pay_date date,earn_amount number);
>
> select empno
> from pay_check
> natural join
> pay_earnings
> using (empno);
>
>
> select empno
> from pay_check c
> natural join
> pay_earnings e
> on (c.empno = e.empno);
>
> are valid.
Received on Sun Mar 23 2003 - 20:50:31 CST