Re: Joining tables in Oracle?

From: Walter Dorninger <walter.dorninger_at_aon.at>
Date: Wed, 27 Dec 2000 10:47:54 +0100
Message-ID: <92cdud$1djf$1@wrath.news.nacamar.de>

Hi Gary,

(1) Inner Join should look something like this:

SELECT ig_dictionary.*
  FROM ig_keywords,
       ig_dictionary,
       ig_keydict
WHERE ig_dictionary.dictionary_id = ig_keydict.dictionary_id

  AND ig_keywords.keyword_id = ig_keydict.keyword_id   AND ig_keywords.keyword_id='123456'

(2) Outer Joins can be performed with the "(+)" Operator

SELECT ename, job, dept.deptno, dname

    FROM emp, dept
    WHERE emp.deptno (+) = dept.deptno;

ENAME JOB DEPTN DNAME
---------- --------- ---------- --------------

CLARK      MANAGER           10 ACCOUNTING
KING       PRESIDENT         10 ACCOUNTING
MILLER     CLERK             10 ACCOUNTING
SMITH      CLERK             20 RESEARCH
ADAMS      CLERK             20 RESEARCH
FORD       ANALYST           20 RESEARCH
SCOTT      ANALYST           20 RESEARCH
JONES      MANAGER           20 RESEARCH
ALLEN      SALESMAN          30 SALES
BLAKE      MANAGER           30 SALES
MARTIN     SALESMAN          30 SALES
JAMES      CLERK             30 SALES
TURNER     SALESMAN          30 SALES
WARD       SALESMAN          30 SALES
                             40 OPERATIONS

another example (taken from the oracle documentation)

SELECT ename, job, dept.deptno, dname

    FROM emp, dept
    WHERE emp.deptno (+) = dept.deptno

        AND job (+) = 'CLERK';

ENAME JOB DEPTNO DNAME
---------- --------- ---------- --------------

MILLER     CLERK             10 ACCOUNTING
SMITH      CLERK             20 RESEARCH
ADAMS      CLERK             20 RESEARCH
JAMES      CLERK             30 SALES
                             40 OPERATIONS

You can find all the oracle documentation on http://technet.oracle.com (free registration)

Hope this helps
Walter
"Gary" <garygfx_at_hotmail.com> wrote in message news:92ba0s$fpe$1_at_plutonium.btinternet.com...
> How can I perform a LEFT join, INNER join, and a RIGHT join in Oracle 8i
> please? I've got some MS Access queries I need to convert to Oracles - it
> seems to be a little limited on the join syntax but I'm sure there's a way
> around it?
>
> For example, here are 2 queries I need to use with Oracle:
>
> SELECT ig_dictionary.*
> FROM ig_keywords
> INNER JOIN (ig_dictionary INNER JOIN ig_keydict ON
> ig_dictionary.dictionary_id = ig_keydict.dictionary_id) ON
> ig_keywords.keyword_id = ig_keydict.keyword_id
> WHERE ig_keywords.keyword_id='123456'
>
> SELECT ig_keywords.keyword_id, ig_keywords.keyword,
> ig_dictionary.dictionary_id, ig_dictionary.title
> FROM ig_keywords
> RIGHT JOIN (ig_dictionary LEFT JOIN ig_keydict ON
> ig_dictionary.dictionary_id = ig_keydict.dictionary_id) ON
> ig_keywords.keyword_id = ig_keydict.keyword_id
> WHERE ig_keywords.keyword_id='123456'
>
> Thanks in advance...
> Gary.
>
>
>
Received on Wed Dec 27 2000 - 03:47:54 CST