{"id":83,"date":"2007-04-02T20:16:34","date_gmt":"2007-04-02T19:16:34","guid":{"rendered":"http:\/\/bar-solutions.com\/wordpress\/?p=83"},"modified":"2007-04-02T20:21:03","modified_gmt":"2007-04-02T19:21:03","slug":"ansi-sql-vs-oracle-sql","status":"publish","type":"post","link":"https:\/\/blog.bar-solutions.com\/?p=83","title":{"rendered":"Ansi SQL vs Oracle SQL"},"content":{"rendered":"

Today I was wondering about whether we should be using Ansi SQL instead of the 'normal' Oracle SQL. Ansi SQL has been supported in Oracle since version 9i<\/em>, but since we are still developing applications on Oracle 8i<\/em> we haven't really given it much thought until now. We are migrating our development database to Oracle9i<\/em> (No 10G<\/em> yet, because our DBA's don't support it yet) and now is the time to consider writing Ansi SQL instead of the 'normal' Oracle SQL.<\/p>\n

What are the pros and cons of Ansi SQL? One of the things that pops to mind is to make our application less 'Oracle locked in'. But will we really achieve that just by using Ansi SQL? Our application(s) rely heavily on the Oracle database and all of it's features. Of course we can use Ansi SQL all the way, but a lot of functions and procedures are deliberately put in backend code (PL\/SQL) because of their speed and their need for database interactivity. Sure, we can rebuild these in say SQL Server or even MySQL, but at this moment we don't use any of those for this application and frankly, I don't see this happening any time soon.<\/p>\n

Another thing is that it may make the queries more readable. All the joins between tables are done in the FROM<\/em> clause instead of in the WHERE<\/em> clause. Now they can be grouped together, although I am used to writing the joins first in my WHERE<\/em> clauses. But it doesn't mean everybody writes in such a way.<\/p>\n

A problem with our current queries can definitely be the outer-joins used. In Oracle SQL you use the plus sign (+) to tell the SQL Engine that it's an outer join. In SQL Server you should use the asterisk (*) operator to do the same. In Ansi SQL you tell the query to LEFT OUTER JOIN<\/em> or RIGHT OUTER JOIN<\/em> one table to another. This is the same in both engines.<\/p>\n

I don't know which syntax I like more. The Oracle SQL version:<\/p>\n

\n

SELECT *
FROM emp, dept
WHERE emp.deptno = dept.deptno;<\/p>\n<\/blockquote>\n

Or the Ansi SQL version:<\/p>\n

\n

SELECT *
FROM emp JOIN dept ON emp.deptno = dept.deptno;<\/p>\n<\/blockquote>\n

I think it just takes some getting used to. Of course there are possibilities in Ansi SQL that are easier to use than doing the same in 'normal' SQL. There's for instance the FULL OUTER JOIN<\/em>. Show all rows from one table that may or may not have a row in the other table and vice versa. Sure this can be achieved in Oracle SQL creating a UNION<\/em> based query, but that would mean that all the other clauses in the query would have to be written twice (or more), which is totally against our Single Point Of Definition<\/em> principle. I know Steven Feuerstein wrote about this in one of his blogs<\/a> on ToadWorld<\/a>, but I think we were using this principle since the last century (somewhere in the 90's).<\/p>\n

I am not sure what to use. Maybe it's better to start getting used to Ansi SQL because it's a better preparation for future versions of the (Oracle) database. And we can be more prepared to move to different databases, although that will include learning a new language as well as a new way to create code in the database. I know I am not a supporter of a 'dumb database' or 'datastore'. I want to use the features in the database as they are available.<\/p>\n","protected":false},"excerpt":{"rendered":"

Today I was wondering about whether we should be using Ansi SQL instead of the 'normal' Oracle SQL. Ansi SQL has been supported in Oracle since version 9i, but since we are still developing applications on Oracle 8i we haven't really given it much thought until now. We are migrating our development database to Oracle9i […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,5,9],"tags":[],"class_list":["post-83","post","type-post","status-publish","format-standard","hentry","category-oracle","category-plsql","category-sql"],"_links":{"self":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/83","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=83"}],"version-history":[{"count":0,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/83\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=83"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=83"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=83"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}