SQL:1999

SQL:1999 (also called SQL 3) was the fourth revision of the SQL database query language. It introduced many new features, many of which required clarifications in the subsequent SQL:2003. In the meanwhile{{clarify|date=April 2023}} SQL:1999 is deprecated.

Summary

The ISO standard documents were published between 1999 and 2002 in several installments, the first one consisting of multiple parts. Unlike previous editions, the standard's name used a colon instead of a hyphen for consistency with the names of other ISO standards. The first installment of SQL:1999 had five parts:

SQL/Framework [http://www.iso.org/iso/iso_catalogue/catalogue_tc/catalogue_detail.htm?csnumber=26196 ISO/IEC 9075-1:1999]
SQL/Foundation [http://www.iso.org/iso/iso_catalogue/catalogue_tc/catalogue_detail.htm?csnumber=26197 ISO/IEC 9075-2:1999]
SQL/CLI : an updated definition of the extension Call Level Interface, originally published in 1995, also known as CLI-95 [http://www.iso.org/iso/iso_catalogue/catalogue_tc/catalogue_detail.htm?csnumber=30609 ISO/IEC 9075-3:1999]
SQL/PSM : an updated definition of the extension Persistent Stored Modules, originally published in 1996, also known as PSM-96 [http://www.iso.org/iso/iso_catalogue/catalogue_tc/catalogue_detail.htm?csnumber=29864 ISO/IEC 9075-4:1999]
SQL/Bindings [http://www.iso.org/iso/iso_catalogue/catalogue_tc/catalogue_detail.htm?csnumber=26198 ISO/IEC 9075-5:1999]

Three more parts, also considered part of SQL:1999 were published subsequently:

SQL/MED Management of External Data (SQL:1999 part 9) [http://www.iso.org/iso/home/store/catalogue_ics/catalogue_detail_ics.htm?csnumber=31370 ISO/IEC 9075-9:2001]
SQL/OLB Object Language Bindings (SQL:1999 part 10) [http://www.iso.org/iso/catalogue_detail.htm?csnumber=30613 ISO/IEC 9075-10:2000]
SQL/JRT SQL Routines and Types using the Java Programming Language (SQL:1999 part 13) [http://www.iso.org/iso/home/store/catalogue_ics/catalogue_detail_ics.htm?csnumber=35340 ISO/IEC 9075-13:2002]

New features

= Data types =

==Boolean data types==

The SQL:1999 standard calls for a Boolean type,.[http://www.iso.org/iso/iso_catalogue/catalogue_tc/catalogue_detail.htm?csnumber=26197 ISO/IEC 9075-2:1999] {{Webarchive|url=https://web.archive.org/web/20161224232956/http://www.iso.org/iso/iso_catalogue/catalogue_tc/catalogue_detail.htm?csnumber=26197 |date=2016-12-24 }} section 4.6 Boolean types IBM Db2 supports boolean values since around 11.1.https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.wn.doc/doc/c0061179.html#c0061179__FP1 Microsoft SQL Server supports storage for booleans using "BIT" data type{{citation needed|date=June 2019}}. MySQL interprets "BOOL" and "BOOLEAN" as a mapping for its native TINYINT(1) type.{{cite web |url=http://dev.mysql.com/doc/refman/5.0/en/other-vendor-data-types.html |title=MySQL :: MySQL 5.0 Reference Manual :: 11.4 Using Data Types from Other Database Engines |publisher=Dev.mysql.com |date=2010-01-09 |accessdate=2014-01-30 |archive-date=2014-02-07 |archive-url=https://web.archive.org/web/20140207011906/http://dev.mysql.com/doc/refman/5.0/en/other-vendor-data-types.html |url-status=live }} PostgreSQL provides a standard conforming Boolean type.{{Cite web |url=http://www.postgresql.org/docs/current/static/datatype-boolean.html |title=PostgreSQL documentation about Boolean Type |access-date=2023-12-06 |archive-date=2018-03-09 |archive-url=https://web.archive.org/web/20180309053449/https://www.postgresql.org/docs/current/static/datatype-boolean.html |url-status=live }} Oracle Database has proper boolean since Release 23.https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/Data-Types.html#GUID-285FFCA8-390D-4FA9-9A51-47B84EF5F83A

== Distinct user-defined types of power ==

Sometimes called just distinct types, these were introduced as an optional feature (S011) to allow existing atomic types to be extended with a distinctive meaning to create a new type and thereby enabling the type checking mechanism to detect some logical errors, e.g. accidentally adding an age to a salary. For example:

create type age as integer FINAL;

create type salary as integer FINAL;

creates two different and incompatible types. The SQL distinct types use name equivalence not structural equivalence like typedefs in C. It's still possible to perform compatible operations on (columns or data) of distinct types by using an explicit type CAST.

Few SQL systems support these. IBM Db2 is one those supporting them.{{cite web|url=http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.intro%2Fsrc%2Ftpc%2Fdb2z_distincttypes.htm |title=IBM Information Management Software for z/OS Solutions Information Center |publisher=Publib.boulder.ibm.com |date= |accessdate=2014-01-30}} Oracle database did not support them as of 2012, recommending instead to emulate them by a one-place structured type.{{cite web |url=http://docs.oracle.com/cd/B19306_01/server.102/b14200/ap_standard_sql003.htm |title=Oracle Compliance To Core SQL:2003 |publisher=Docs.oracle.com |date= |accessdate=2014-01-30 |archive-date=2013-12-02 |archive-url=https://web.archive.org/web/20131202042209/http://docs.oracle.com/cd/B19306_01/server.102/b14200/ap_standard_sql003.htm |url-status=live }}

== Structured user-defined types ==

These are the backbone of the object–relational database extension in SQL:1999. They are analogous to classes in objected-oriented programming languages. SQL:1999 allows only single inheritance.

=Common table expressions and recursive queries=

SQL:1999 added a WITH [RECURSIVE] construct allowing recursive queries, like transitive closure, to be specified in the query language itself; see common table expressions.

= Some [[OLAP]] capabilities =

GROUP BY was extended with ROLLUP, CUBE, and GROUPING SETS.

= Role-based access control =

Full support for RBAC via CREATE ROLE.

= Keywords =

SQL:1999 introduced the UNNEST keyword.

{{cite book

| last1 = Jones

| first1 = Arie

| last2 = Stephens

| first2 = Ryan K.

| last3 = Plew

| first3 = Ronald R.

| last4 = Garrett

| first4 = Robert F.

| last5 = Kriegel

| first5 = Alex

| chapter = Appendix B ANSI and Vendor Keywords

| title = SQL Functions Programmer's Reference

| url = https://books.google.com/books?id=QcE1qCobUfcC

| publisher = John Wiley & Sons

| publication-date = 2005

| page = 680

| isbn = 9780764598074

| accessdate = 2016-05-16

}}