What is 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

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.