EXCEPTION JOIN in Oracle 11g

In my ongoing transition from decades as a DB2 developer to now being an Oracle developer, I'm having a bit of a problem with some Oracle syntax.

The Oracle documentation I see on-line seems to indicate that EXCEPTION JOIN is supported in Oracle, but when I try to use that in Oracle 11g, it fails. What am I missing?

select t1.*
  from MySchema.TestingStuff_Table1 t1
;

     THEPK FIRSTNAME          
---------- --------------------
         1 Fred                 
         2 Wilma                
         3 Barney               
         4 Betty                

select t2.*
  from MySchema.TestingStuff_ExcludeThese t2
;

     THEPK
----------
         1 

select t1.*
  from MySchema.TestingStuff_Table1 t1
 where not exists (
   select *
     from MySchema.testingstuff_ExcludeThese t2
    where t2.thepk = t1.thepk
)
;

     THEPK FIRSTNAME          
---------- --------------------
         2 Wilma                
         3 Barney               
         4 Betty                

select t1.* 
  from MySchema.TestingStuff_Table1 t1
  exception join MySchema.TestingStuff_ExcludeThese t2
    on t2.thePK = t1.thePK
;

Error at Command Line:3 Column:3
Error report:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"

Open in new window


Thanks in advance!
DaveSlash
LVL 18
Dave FordSoftware Developer / Database AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Not up on all my Oracle ANSI syntax but I've never seen "exception join".

Make sure the docs are for the Oracle Database and not some other product Oracle owns and stamps their name on.

If you can provide the link that says it is valid syntax, we'll take a look.

Oracle Database documentation link is here for 11g:
https://docs.oracle.com/cd/E11882_01/index.htm

12c:
http://docs.oracle.com/database/121/index.htm
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I confirm that EXCEPTION JOIN does exist as such in DB2, but not in Oracle
http://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/sqlp/rbafymstexjo.htm

from there, I can say that the syntax is a shortcode:
DB2: SELECT ... FROM table1 t1 LEFT EXCEPTION JOIN table2 T2 ON t1.field = t2.field
ORA: SELECT ... FROM table1 t1 LEFT JOIN table2 t2 ON  t1.field = t2.field WHERE t2.field IS NULL
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dave FordSoftware Developer / Database AdministratorAuthor Commented:
Thanks, angelIII. I appreciate the guidance. I rarely have the need to use an Exception Join, but when I do, it's a convenient and elegant solution. Thanks also for the "functional equivalent". I think that'll work for me.

Regards,
DaveSlash
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.