Oracle SQL

[MobileCheese]

Ive got about 8 years experience writing SQL and PL/SQL specifically in Oracle and use it day in day out. However I went for new gig last week and failed the frigging SQL test . The problem was they didnt like the way I did outer joins, Ive always done them as follows,

TABLE1.TITS_ID = TABLE2.TITS_ID(+)

However they said they do it ANSI standard ie OUTER JOIN ON keyword in the FROM clause as opposed to having the joins in the WHERE clause like I write my SQL.

Question I have is Oracle SQL moving towards ANSI standard, should I start to change the syntax I use ? Confused. All of my clients have been IB's and all of them use old fashioned SQL, the client who rejected me based on the SQL test were a investment fund manager, and the IT department was 90% in the US, could it be that the yanks are moving more to ANSI standard quicker than Europe ?

I really wanted the gig 12 months with the biggest rate I would have ever earnt

[thunderlizard]

You were unlucky there. those joins are pretty new in Oracle. But if a client had a preference, I'd follow it.

[TheFaQQer]

Oracle brought in the ANSI standard in 9i, but the older method is backwards compatible (and I suggest that it always will be, for companies migrating from previous versions).

Two advantages of the ANSI standard are that it becomes immediately clear in the SELECT statement what is a data selection criterion and what is a join condition, plus I think there are some things you can do in the ANSI statement that the plus notation does not allow.

I still use the plus notation, though.

[SallyAnne]

I've never use them...I always use the notation you described MC in the where clause.

I'm sorry to hear this (as you wanted the gig) but I think if they were that pedantic they'd be utter c*cks to work for! You've probably had a narrow escape mate!
I mean, why couldn't they just tell you they prefered it done that way once you joined them? Why would they not give you the gig because of that?!!

[Cowboy Bob]

ALWAYS use ANSI standard as much as possible.

1) It's easier to read and understand
2) It is a lot more flexible in terms of what you can do without mixing and matching
3) The old Oracle 8 way is well out of date - most Oracle installations are now 9i and upwards
3) It makes porting logic to another DB one hell of a lot easier

I'd also insist you used ANSI standard. I wouldn't refuse you the job, but you'd need to be prepared to change your coding style (and show that you understood it).

[MobileCheese]

The client had actually only started using Oracle about 12 months ago, previously they were Sybase, their recent switch to Oracle probably explains why they were 100% ANSI standard. Suppose it something Im going to have to watch out for, ask the client during interview if before the techy test or get the agency to find out.

Im going to spend sometime comming up to speed with ANSI standard or at least know the basics. For people who have used both old skool and ANSI what do they prefer, can you use ANSI SQL in PL/SQL.

[SallyAnne]

I always work with Oracle Applications...and I structure my code to reflect theirs. And they NEVER put joins in the FROM clause! Ever!

Are there any efficiency benefits? (As thats all I'm really bothered about).

[Cowboy Bob]

Are there any efficiency benefits? (As thats all I'm really bothered about).

I'd have thought that execution plans for dynamic SQL would be easier to calculate if all the joins are in the FROM clause rather than within the SELECT expression or a mixture of the two.

[Cowboy Bob]

can you use ANSI SQL in PL/SQL.

Yes

[MarillionFan]

Well there you go. I didnt realise it was the difference in standards I always took the difference as programming style and have done either depending on the client.