What is ANSI SQL

ANSI means American National Standards Institute (http://www.ansi.org)

Evolution of ANSI SQL

SQL is an integral part of many modern RDBMS like Oracle, DB2, Microsoft SQL Server, MySQL etc. Each vendor developed their own SQL syntax for their own products. All over the world many government, public and private agencies use different database products from different vendors and each develop system based on vendor dependent SQLs. So ANSI developed a common standard for SQL which runs in all modern vendor databases.

Advantages

  • Work in all modern databases.
  • Easy to integrate different systems.
  • Easy to migrate from one vendor database to another.
  • Less maintenance cost.

Disadvantages

  • Relatively complex syntax compared to vendor SQLs.
  • Higher development cost.

Explanation with example

SQL to find all employees from Marketing Department. I have written two queries one in Oracle SQL format and another is ANSI SQL format

1) Standard SQL style

select e.empno,e.empname,e.salary,d.deptname
from emp e,dept d 
where e.deptno = d.deptno
and d.deptno = 2;

2) ANSI Style

select e.empno,e.empname,e.salary,d.deptname
from emp e join dept d
on (e.deptno = d.deptno)
where d.deptno = 2;

Assume a company using Db2 database and writing all their SQL in non-ANSI standard, and one fine morning management decide to port some applications from Db2 to Oracle or Microsoft SQL Server, most of the SQLs need to re-write with respect to the new database standard. If all SQL follows ANSI standard it would be quiet easy to migrate from any vendor database to another.

Also ANSI SQL maintenance is easy because a lot of employees might come from different platforms and every developer can easily follow if every body adhere to the standard.

As you know that ANSI SQL syntax is little difficult to follow initially. But once every body used to it, it is quite easy. Though it is considered as more complex than non-ANSI SQL standard. If you see the above queries both looks almost similar, but for larger queries having too many joins and where conditions it would be little complex as you think.

Another disadvantage is ANSI SQL will not have all features of vendor database SQLs because ANSI is a common standard and it is very difficult to integrate all features of modern database SQLs.

See the below Oracle SQL to print 1 to 10 using only SQL.

select level from dual connect by level <= 10; 

The same feature is not available in ANSI SQL ( As of today ) and in another vendor databases corresponding SQL will be much complex than above.

Finally choosing and adhering ANSI SQL standard is an important factor you need to decide before you start writing the SQLs. Personally I would say it is very ideal for small companies and websites to follow ANSI SQL because you cannot predict when your base database vendor ( Actually Open Source databases ) giving you some trouble.

Related Posts
What is SQL
What is Oracle Database

Advertisements

Author: sqlandplsql

To learn more about the Oracle, SQL, PL SQL, Performance Tuning, Database Modeling, Ubuntu, MySQL etc .Suggestions, comments, feedbacks and referrals are highly appreciated.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s