Why Does No Database Fully Support Ansi or Iso SQL Standards

Why does no database fully support ANSI or ISO SQL standards?

In the software industry you have some standards that are really standards, i.e., products that don't comply with them just don't work. File specifications fall into that category. But then you also have "standards" that are more like guidelines: they may defined as standards with point-by-point definitions, but routinely implemented only partially or with significant differences. Web development is full of such "standards", like HTML, CSS and "ECMAScript" where different vendors (i.e. web browsers) implement the standards differently.

The variation causes headaches, but the standardization still provides benefits. Imagine if there were no HTML standard at all and each browser used its own markup language. Likewise, imagine if there were no SQL standard and each database vendor used its own completely proprietary querying language. There would be much more vendor lock-in, and developers would have a much harder time working with more than one product.

So, no, ANSI SQL doesn't serve the same purpose as ANSI standards do in other industries. But it does serve a useful purpose nonetheless.

Is 'INDEX' valid SQL ANSI ISO standard keyword / reserved word?

There is no ANSI standard for SQL language used to create, alter, or manage indexes. So no, INDEX is not a keyword (reserved word) per ANSI standards.

Kevin Kline specifically backs me up here in his book SQL in a Nutshell. He points this out as one of the reasons the syntax for creating indexes varies greatly among vendors.

As further circumstantial evidence you'll also note a variety of vendors mention in their SQL documentation that statements regarding INDEXs are extensions to the ANSI standard. For example see IBM doc here for ALTER INDEX.

This is also a handy list of the ANSI SQL reserved words - but only up to 2003.

---

As a side note: the only time I've ever seen ANSI mentioned at all with regards to indexes is when talking about how a unique index (often simply a unique constraint) treats null values. According to ANSI a null does not equal a null; therefore a unique index should allow multiple null values since per ANSI they do not equal each other. Some engines follow this rule - others do not. The ANSI standard in this case only refers to whether two nulls are equal or unique... the standard has nothing to do with the index. There may be other ANSI standards that have a similar effect on INDEX but nothing regarding the DDL surrounding them.

Reasons for SQL differences

It's a form of "Stealth lock-in". Joel goes into great detail here:

• http://www.joelonsoftware.com/articles/fog0000000056.html
• http://www.joelonsoftware.com/articles/fog0000000052.html

Companies end up tying their business functionality to non-standard or weird unsupported functionality in their implementation, this restricts their ability to move away from their vendor to a competitor.

On the other hand, it's pretty short-sighted because anyone with half a brain will tend to abstract away the proprietary pieces, or avoid the lock-in altogether, if it gets too egregious.

Will ANSI JOIN vs. non-ANSI JOIN queries perform differently?

The two queries are the same, except the second is ANSI-92 SQL syntax and the first is the older SQL syntax which didn't incorporate the join clause. They should produce exactly the same internal query plan, although you may like to check.

You should use the ANSI-92 syntax for several of reasons

• The use of the JOIN clause separates
  the relationship logic from the
  filter logic (the WHERE) and is thus
  cleaner and easier to understand.
• It doesn't matter with this particular query, but there are a few circumstances where the older outer join syntax (using + ) is ambiguous and the query results are hence implementation dependent - or the query cannot be resolved at all. These do not occur with ANSI-92
• It's good practice as most developers and dba's will use ANSI-92 nowadays and you should follow the standard. Certainly all modern query tools will generate ANSI-92.
• As pointed out by @gbn, it does tend to avoid accidental cross joins.

Myself I resisted ANSI-92 for some time as there is a slight conceptual advantage to the old syntax as it's a easier to envisage the SQL as a mass Cartesian join of all tables used followed by a filtering operation - a mental technique that can be useful for grasping what a SQL query is doing. However I decided a few years ago that I needed to move with the times and after a relatively short adjustment period I now strongly prefer it - predominantly because of the first reason given above. The only place that one should depart from the ANSI-92 syntax, or rather not use the option, is with natural joins which are implicitly dangerous.

Does INTERSECT operator exist in the SQL standard?

Your professor is either wrong, or else you misunderstood what they said. INTERSECT is not supported in every implementation of SQL, but it is the standard.

I checked my copy of "Understanding the New SQL: A Complete Guide" by Jim Melton and Alan R. Simon (1993) which covers SQL-92.

Page 171 says:

The INTERSECT and EXCEPT Operators

INTERSECT returns all rows that exist in the intersection of two tables; that is, in both tables.

SELECT * 
  FROM music_titles
INTERSECT
SELECT * 
  FROM discontinued_albums;

The preceding query will return, for example, all discontinued albums that have been re-released.

Here's a link to Google Books with the word INTERSECT highlighted in a search: https://www.google.com/books/edition/Understanding_the_New_SQL/ZOOMSTZ4T_QC?bsq=intersect&gbpv=1

I also checked my copy of "SQL-99 Complete, Really" by Peter Gulutzan and Trudy Pelzer (1999). It also documents INTERSECT.

Difference between Oracle's plus (+) notation and ansi JOIN notation?

AFAIK, the (+) notation is only present for backwards compatibility because Oracle debuted it before the ANSI standard for joins was put in place. It's specific to Oracle and you should avoid using it in new code when there's an equivalent standards-compliant version available.

It seems there are differences between the two, and the (+) notation has restrictions that the ANSI join syntax does not have. Oracle themselves recommend that you not use the (+) notation.
Full description here in the Oracle® Database SQL Language Reference
11g Release 1 (11.1):

Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions, which do not apply to the FROM clause OUTER JOIN syntax:

• You cannot specify the (+) operator in a query block that also contains FROM clause join syntax.
• The (+) operator can appear only in the WHERE clause or, in the context of left-correlation (when specifying the TABLE clause) in the FROM clause, and can be applied only to a column of a table or view.
• If A and B are joined by multiple join conditions, then you must use the (+) operator in all of these conditions. If you do not, then Oracle Database will return only the rows resulting from a simple join, but without a warning or error to advise you that you do not have the results of an outer join.
• The (+) operator does not produce an outer join if you specify one table in the outer query and the other table in an inner query.
• You cannot use the (+) operator to outer-join a table to itself, although self joins are valid.

For example, the following statement is not valid:

SELECT employee_id, manager_id
FROM employees
WHERE employees.manager_id(+) = employees.employee_id;

However, the following self join is valid:

SELECT e1.employee_id, e1.manager_id, e2.employee_id
FROM employees e1, employees e2
WHERE e1.manager_id(+) = e2.employee_id;

• The (+) operator can be applied only to a column, not to an arbitrary expression. However, an arbitrary expression can contain one or more columns marked with the (+) operator.
• A WHERE condition containing the (+) operator cannot be combined with another condition using the OR logical operator.
• A WHERE condition cannot use the IN comparison condition to compare a column marked with the (+) operator with an expression.

If the WHERE clause contains a condition that compares a column from table B with a constant, then the (+) operator must be applied to the column so that Oracle returns the rows from table A for which it has generated nulls for this column. Otherwise Oracle returns only the results of a simple join.

In a query that performs outer joins of more than two pairs of tables, a single table can be the null-generated table for only one other table. For this reason, you cannot apply the (+) operator to columns of B in the join condition for A and B and the join condition for B and C. Refer to SELECT for the syntax for an outer join.

---

Related Topics