ANSI SQL vs. Oracle’s SQL

Prior to the adoption of SQL as a standard by the American National Standards Institute (ANSI), each database management system (DBMS) had its own unique command language and syntax. SQL was adopted as an ANSI standard in 1986. The International Standards Organization (ISO) adopted it in 1987. This standard enables database portability.

Most commercial database vendors support the ANSI standard. However, most of these vendors also extend the standard with the addition of proprietary features. The Oracle version of SQL has differences from the ANSI standard version. For example, Oracle does not support either the full OUTER JOIN or the NATURAL JOIN of ANSI SQL.

When you have a choice, the vendor specific proprietary solution may offer better performance or convenience. On the other hand, the proprietary feature also limits or eliminates portability.

Think about the strengths and weaknesses of both the standard ANSI and proprietary Oracle approach to SQL. In particular, think about the different ways ANSI and Oracle support joins.

With these thoughts in mind:

Consider a situation where you would use a join to retrieve data from two tables in an Oracle database, such as the stock_count for an oil filter from the product table and the vendor for that oil filter from the vendor table. Would you prefer to use ANSI standard SQL or the proprietary Oracle SQL? What factors influenced your decision? Describe a situation where you would consider using the other option.