Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.tools -> Re: Joining tables in Oracle?
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_idAND 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
![]() |
![]() |