| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: ORA-00933: SQL command not properly ended
On Mon, 10 Mar 2003 14:33:46 +0000, Christina wrote:
>>
>>
>> What version of Oracle are you using?? The inner join syntax only became
>> possible with 9i. And what you've written works just fine in 9iR2:
>>
>> SQL> select * from emp inner join dept on emp.deptno=dept.deptno;
>>
>> EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
>> ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- -------------- ------------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 20 RESEARCH DALLAS
>> 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 30 SALES CHICAGO
>>
>> (With apologies for the wrapping).
>>
>> Regards
>> HJR
> > > > I'm using Oracle 8i - 8.1.6... Is there any other way that can you > join 2 or 3 tables by using Oracle 8i? > > Thanks, > Christina
I hate to be didactic about this: but *version number DOES matter*. So include it in EVERY post.
There is of course syntax for doing inner and outer joins in earlier versions of Oracle (but kiss goodbye to the idea of doing full outer joins, except via a Union).
It all involves being able to place a "+" in completely the wrong place.
For example: show me all records from dept, whether they have a matching employee or not:
SQL> select * from emp, dept
2 where emp.deptno(+)=dept.deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- -------------- ------------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 02-APR-81 2975 20 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 20 RESEARCH DALLAS
7839 KING PRESIDENT 17-NOV-81 5000 10 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 30 SALES CHICAGO
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 03-DEC-81 950 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 03-DEC-81 3000 20 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 23-JAN-82 1300 10 10 ACCOUNTING NEW YORK
40 OPERATIONS BOSTON
Now, what you wanted, of course, was all DEPTS, regardless of EMPS, so
naturally you stick the (+) on the EMP side of the where clause.
I might get confused here (there's a single malt waiting for me), but I would call this a right outer join (show me all records from the table that appears on the right-hand side of the from clause), and the + goes next to the left-hand table in the where clause.
The left-outer join does exactly the opposite (but I can't think of an example to show you in a hurry because of that single malt that is singing to me gently: "Drink me"): stick the plus sign next to the right-most table in the where clause. For example, show me all records in table A even when not matched to records in table B would be:
select * from A, B
where A.col=B.col(+);
Don't ask me why it is completely the wrong way around. Someone here will post pretty promptly that there's a perfectly logical explanation, but all I can say they haven't been doing this under the influence of shingle scmaltz whizzies. And they're wrong, schmong whatever anyway.
Have a hic ady
Regarz
Hic
Received on Mon Mar 10 2003 - 21:15:21 CST
 |
 |