January 24, 2024

ANSI vs. Oracle Proprietary SQL

By Rich Soule

On Tuesdays Cary Millsap runs a meeting for Method-R customers to chat about Oracle stuff. Often he has something prepared, but this Tuesday he didn’t. Doug Gault decided to share an image that finally helped him get his head around the ANSI SQL syntax. Doug has been around the Oracle world for a long time but he’s always been able to work with Oracle proprietary SQL so he never really learned the ANSI SQL syntax. Recently he got assigned to a project where ANSI SQL is mandated so he had to get everything straight in his head. He shared an image that he had created from some training and we all took a look at it. Me, being me, I immediately jumped in with what I thought would be improvements to the image. I was challenged to come up with a better image, and so, I created the below.

My hope is that this will help some folks move away from the horrible (in my opinion) Oracle propriety SQL syntax to the totally awesome ANSI SQL syntax. I think the Oracle syntax is horrible because where clauses in queries end up doing two things; joining table AND filtering rows. With the ANSI syntax, join clauses join tables and where clauses only filter rows.

A note on the above: I used the preferred USING syntax to join tables for the ANSI queries:

join using (deptno)

instead of the ON syntax to join tables

join on e.deptno = d.deptno

I believe this is easier to read and understand and, in general, less code is better code, and this is smaller. If you use the USING syntax just note that you no longer associate the column with one table or another in the other clauses (like SELECT or WHERE) but instead leave it unconstrained. For example:

select deptno, e.ename, d.dname
  from emp e
  join dept d using (deptno)
 where deptno > 20;

If you were to qualify the DEPTNO column in either the select clause or the where clause (d.deptno for example) you’d get an ORA-25154: column part of USING clause cannot have qualifier message.

Let me know if this is helpful for you!

This entry was posted on Wednesday, January 24th, 2024 at 9:35 am and tagged with , , , , , and posted in Code, Oracle Database, SQL. You can follow any responses to this entry through the RSS 2.0 feed.

2 responses to “ANSI vs. Oracle Proprietary SQL”

  • EA's avatar EA
    September 6th, 2024 at 1:48 pm

    Very helpful for old school folks like me!

    Can you please extend your writeup on how to use ANSI syntax for multiple table joins? Working with Oracle syntax, 20 years, I never had a need to learn ANSI.. But the new generation and new code has so much of ANSI that I have no choice 🙂

    I get confused when I see one table next to FROM and then all the rest of the tables seem to get joined to that same table with INNER or OUTER join.

    In this example from someone’s code at work, are all tables getting joined to PUB_NETWORK_FUNCTION_GROUP only?

    What if I did not want to join PUB_NETWORK_FUNCTION_GROUP with say LOCATION , but join LOCATION only PUB_MTSO_INFRA?

    ==============================

    FROM PUB_NETWORK_FUNCTION_GROUP dgpair

    INNER JOIN ccdm.PUBLISH epd ON epd.id = dgpair.publish_id

    LEFT OUTER JOIN PUB_MTSO_INFRA deva ON dgpair.nf_group_id = deva.NETWORK_FUNCTION_GROUP_ID

    AND deva.publish_id = dgpair.publish_id AND (deva.device_group_role = 'A')

    LEFT OUTER JOIN PUB_MTSO_INFRA devb ON dgpair.nf_group_id = devb.NETWORK_FUNCTION_GROUP_ID

    AND devb.publish_id = dgpair.publish_id AND (devb.device_group_role = 'B')

    LEFT OUTER JOIN PUB_MTSO_INFRA devHub ON devHub.supergroup_id = deva.supergroup_id

    AND devHub.publish_id = deva.publish_id AND devHub.supergroup_role = 'HUB-PAIR'

    AND devHub.device_group_role IN ('A')

    LEFT OUTER JOIN ccdm.LOCATION loc_a ON deva.location_id = loc_a.id

    LEFT OUTER JOIN ccdm.LOCATION loc_Hub ON devHub.location_id = loc_Hub.id

    LEFT OUTER JOIN ccdm.network_function_model_MD md_a ON deva.model_id = md_a.id

    LEFT OUTER JOIN PUB_NETWORK_FUNCTION_GROUP dgp ON dgp.containing_group_id = deva.supergroup_id AND dgp.publish_id = deva.publish_id

    ===================================

    In Oracle syntax we can join a pair of tables with a join condition and then another pair and then another pair … )

    Thank you!

  • Rich Soule's avatar Rich Soule
    September 7th, 2024 at 12:28 pm

    EA,

    If you want to limit joins then a common table expression (also known as a WITH clause) is a great way to do that. It’s also a great way to add maintainability into your queries. Looking at your join conditions above, it would take me a bit to figure out what the business needed in the query. If, however, you joined two or three tables at a time and then gave that result a good business name using a WITH clause, this query would be much easier to understand.

    Rich

Leave a comment