ISO/IEC 9075-2:2023
(Main)Information technology — Database languages SQL — Part 2: Foundation (SQL/Foundation)
Information technology — Database languages SQL — Part 2: Foundation (SQL/Foundation)
Technologies de l'information — Langages de base de données SQL — Partie 2: Fondations (SQL/Fondations)
General Information
Standards Content (Sample)
INTERNATIONAL ISO/IEC
STANDARD 9075-2
Sixth edition
2023-06
Information technology — Database
languages SQL —
Part 2:
Foundation (SQL/Foundation)
Technologies de l'information — Langages de base de données SQL —
Partie 2: Fondations (SQL/Fondations)
Reference number
ISO/IEC 9075-2:2023(E)
© ISO/IEC 2023
---------------------- Page: 1 ----------------------
ISO/IEC 9075-2:2023(E)
COPYRIGHT PROTECTED DOCUMENT
© ISO/IEC 2023
All rights reserved. Unless otherwise specified, or required in the context of its implementation, no part of this publication may
be reproduced or utilized otherwise in any form or by any means, electronic or mechanical, including photocopying, or posting on
the internet or an intranet, without prior written permission. Permission can be requested from either ISO at the address below
or ISO’s member body in the country of the requester.
ISO copyright office
CP 401 • Ch. de Blandonnet 8
CH-1214 Vernier, Geneva
Phone: +41 22 749 01 11
Email: copyright@iso.org
Website: www.iso.org
Published in Switzerland
ii
© ISO/IEC 2023 – All rights reserved
---------------------- Page: 2 ----------------------
ISO/IEC9075-2:2023(E)
Contents Page
Foreword.xix
Introduction.xxi
1 Scope.1
2 Normativereferences.2
3 Termsanddefinitions.4
3.1 DefinitionstakenfromISO/IEC10646:2020.4
3.2 DefinitionstakenfromISO/IEC14651:2020.5
3.3 DefinitionstakenfromISO8601-1:2019.5
3.4 DefinitionstakenfromXQueryandXPathFunctionsandOperators3.1.7
3.5 Definitionsprovidedinthisdocument.7
4 Concepts.15
4.1 Notationsandconventions.15
4.1.1 Notations.15
4.1.2 Useofterms.15
4.2 Data types.16
4.2.1 Generalintroductiontodatatypes.16
4.2.2 Namingofpredefinedtypes.17
4.2.3 Hostlanguagedatatypes.18
4.2.4 Datatypeterminology.18
4.2.5 Propertiesofdistinct.20
4.3 Characterstrings.21
4.3.1 Introductiontocharacterstrings.21
4.3.2 Comparisonofcharacterstrings.21
4.3.3 Operationsinvolvingcharacterstrings.22
4.3.3.1 Regularexpressionsyntaxes.22
4.3.3.2 Operatorsthatoperateoncharacterstringsandreturncharacterstrings.22
4.3.3.3 Otheroperatorsinvolvingcharacterstrings.25
4.3.3.4 Operationsinvolvinglargeobjectcharacterstrings.26
4.3.4 Characterrepertoires.27
4.3.5 Characterencodingforms.28
4.3.6 Collations.28
4.3.7 Character sets.29
4.3.8 Universalcharactersets.31
4.4 Binary strings.31
4.4.1 Introductiontobinarystrings.31
4.4.2 Binarystringcomparison.31
4.4.3 Operationsinvolvingbinarystrings.31
4.4.3.1 Operatorsthatoperateonbinarystringsandreturnbinarystrings.31
©ISO/IEC2023–Allrightsreserved iii
---------------------- Page: 3 ----------------------
ISO/IEC9075-2:2023(E)
4.4.3.2 Otheroperatorsinvolvingbinarystrings.32
4.5 Numbers.32
4.5.1 Introductiontonumbers.32
4.5.2 Characteristicsofnumbers.33
4.5.3 Operationsinvolvingnumbers.34
4.6 Boolean types.35
4.6.1 IntroductiontoBooleantypes.35
4.6.2 ComparisonandassignmentofBooleans.36
4.6.3 OperationsinvolvingBooleans.36
4.6.3.1 OperationsonBooleansthatreturnBooleans.36
4.6.3.2 OtheroperatorsinvolvingBooleans.36
4.7 Datetimesandintervals.36
4.7.1 Introductiontodatetimesandintervals.36
4.7.2 Datetimes.37
4.7.3 Intervals.39
4.7.4 Operationsinvolvingdatetimesandintervals.41
4.8 JSONtypes.41
4.8.1 IntroductiontoJSONtypes.41
4.8.2 ComparisonandassignmentofJSONvalues.42
4.8.3 OperationsinvolvingJSONvalues.43
4.9 User-defined types.43
4.9.1 Introductiontouser-definedtypes.43
4.9.2 Distincttypes.44
4.9.3 Structured types.44
4.9.3.1 Introductiontostructuredtypes.44
4.9.3.2 Observerfunctionsandmutatorfunctions.44
4.9.3.3 Constructors.44
4.9.3.4 Subtypesandsupertypes.45
4.9.4 Methods.46
4.9.5 User-definedtypecomparisonandassignment.47
4.9.6 Transformsforuser-definedtypes.48
4.9.7 User-definedtypedescriptor.48
4.10 Row types.50
4.11 Referencetypes.50
4.11.1 Introductiontoreferencetypes.50
4.11.2 Operationsinvolvingreferences.51
4.12 Collectiontypes.51
4.12.1 Introductiontocollectiontypes.51
4.12.2 Arrays.52
4.12.3 Multisets.52
4.12.4 Collectioncomparisonandassignment.52
4.12.5 Operationsinvolvingarrays.53
4.12.5.1 Operatorsthatoperateonarrayvaluesandreturnarrayelements.53
4.12.5.2 Operatorsthatoperateonarrayvaluesandreturnarrayvalues.53
4.12.5.3 Operatorsthatoperateonarrayvaluesandreturnnumbers.53
4.12.6 Operationsinvolvingmultisets.53
4.12.6.1 Operatorsthatoperateonmultisetsandreturnmultisetelements.53
iv ©ISO/IEC2023–Allrightsreserved
---------------------- Page: 4 ----------------------
ISO/IEC9075-2:2023(E)
4.12.6.2 Operatorsthatoperateonmultisetsandreturnmultisets.53
4.12.6.3 Operatorsthatoperateonmultisetvaluesandreturnnumbers.54
4.13 Dataconversions.54
4.14 Domains.55
4.15 Columns,fields,andattributes.55
4.16 Periods.57
4.16.1 Introductiontoperiods.57
4.16.2 Operationsinvolvingperiods.58
4.17 Tables.58
4.17.1 Introductiontotables.58
4.17.2 Basetables.58
4.17.2.1 Introductiontobasetables.58
4.17.2.2 Regularpersistentbasetables.59
4.17.2.3 System-versioned tables.59
4.17.2.4 Temporary tables.59
4.17.3 Derivedtables.60
4.17.4 Transienttables.61
4.17.5 Uniqueidentificationoftables.61
4.17.6 Tableupdatability.61
4.17.7 Table descriptors.62
4.17.8 Syntacticanalysisofderivedtablesandcursors.63
4.17.9 Referenceabletables,subtables,andsupertables.65
4.17.10 Operationsinvolvingtables.66
4.17.11 Rangevariables.69
4.17.12 Identitycolumns.70
4.17.13 Basecolumnsandgeneratedcolumns.70
4.17.14 Grouped tables.70
4.17.15 Windowed tables.71
4.18 Dataanalysisoperations.72
4.18.1 Introductiontodataanalysisoperations.72
4.18.2 Groupfunctions.72
4.18.3 Windowfunctions.73
4.18.4 Aggregate functions.75
4.18.5 Rowpatternmeasures.77
4.19 Rowpatternmatching.78
4.19.1 Introductiontorowpatternmatching.78
4.19.2 Matchingrowswithapattern.78
4.19.3 Rowpatternmatchingillustrated.79
4.19.4 Rowpatternpartitioning.83
4.19.5 Rowordering.83
4.19.6 Rowpatternmeasurecolumns.83
4.19.7 Numberofrowspermatch.83
4.19.8 Skippingrowsaftermatching.84
4.20 Rowpatterns.84
4.21 Unionsofrowpatternvariables.85
4.22 DefiningBooleanconditions.85
4.23 Scalarexpressionsinrowpatternmatching.86
©ISO/IEC2023–Allrightsreserved v
---------------------- Page: 5 ----------------------
ISO/IEC9075-2:2023(E)
4.23.1 Introductiontoscalarsinrowpatternmatching.86
4.23.2 Runningvs.finalsemantics.86
4.23.3 Rowpatternnavigationoperations.87
4.23.4 Rowpatternclassifierfunction.87
4.23.5 Rowpatternmatchnumberfunction.87
4.24 Determinism.87
4.25 Integrityconstraints.88
4.25.1 Overviewofintegrityconstraints.88
4.25.2 Checkingofconstraints.88
4.25.3 Tableconstraints.89
4.25.3.1 Introductiontotableconstraints.89
4.25.3.2 Uniqueconstraints.89
4.25.3.3 Referentialconstraints.90
4.25.3.4 Tablecheckconstraints.93
4.25.4 Domainconstraints.93
4.25.5 Assertions.93
4.26 Functionaldependencies.94
4.26.1 Overviewoffunctionaldependencyrulesandnotations.94
4.26.2 Generalrulesanddefinitions.94
4.26.3 Knownfunctionaldependenciesinabasetable.95
4.26.4 Knownfunctionaldependenciesinaviewedtable.96
4.26.5 Knownfunctionaldependenciesinatransitiontable.96
4.26.6 Knownfunctionaldependenciesin.96
4.26.7 Knownfunctionaldependenciesina.96
4.26.8 Knownfunctionaldependenciesina.98
4.26.9 Knownfunctionaldependenciesina.99
4.26.10 Knownfunctionaldependenciesina.99
4.26.11 Knownfunctionaldependenciesintheresultofa.99
4.26.12 Knownfunctionaldependenciesintheresultofa.99
4.26.13 Knownfunctionaldependenciesintheresultofa.100
4.26.14 Knownfunctionaldependenciesintheresultofa.100
4.26.15 Knownfunctionaldependenciesina.100
4.26.16 Knownfunctionaldependenciesina.101
4.27 Candidate keys.101
4.28 SQL-schemas.102
4.29 Sequence generators.103
4.29.1 Generaldescriptionofsequencegenerators.103
4.29.2 Operationsinvolvingsequencegenerators.104
4.30 SQL-clientmodules.104
4.31 Embeddedsyntax.105
4.32 DynamicSQLconcepts.106
4.32.1 IntroductiontodynamicSQL.106
4.32.2 OverviewofdynamicSQLforconstructedSQL-statements.106
4.32.3 OverviewofdynamicSQLforpolymorphictablefunctions.107
4.32.4 DynamicSQLstatementsanddescriptorareas.107
4.33 DirectinvocationofSQL.109
4.34 Externally-invokedprocedures.109
vi ©ISO/IEC2023–Allrightsreserved
---------------------- Page: 6 ----------------------
ISO/IEC9075-2:2023(E)
4.35 SQL-invokedroutines.109
4.35.1 OverviewofSQL-invokedroutines.109
4.35.2 CharacteristicsofSQL-invokedroutines.111
4.35.3 ExecutionofconventionalSQL-invokedroutines.114
4.35.4 Invocationofpolymorphictablefunctions.115
4.35.5 Routine descriptors.119
4.35.6 ResultsetsreturnedbySQL-invokedprocedures.122
4.36 SQL-paths.123
4.37 Hostparameters.123
4.37.1 Overviewofhostparameters.123
4.37.2 Statusparameters.123
4.37.3 Dataparameters.124
4.37.4 Indicator parameters.124
4.37.5 Locators.124
4.38 Diagnostics area.125
4.39 Host languages.126
4.40 Cursors.127
4.40.1 Generaldescriptionofcursors.127
4.40.2 Operationsonandusingcursors.131
4.41 SQL-statements.132
4.41.1 ClassesofSQL-statements.132
4.41.2 SQL-statementsclassifiedbyfunction.133
4.41.2.1 SQL-schemastatements.133
4.41.2.2 SQL-data statements.134
4.41.2.3 SQL-datachangestatements.135
4.41.2.4 SQL-transaction statements.136
4.41.2.5 SQL-connectionstatements.136
4.41.2.6 SQL-controlstatements.136
4.41.2.7 SQL-sessionstatements.136
4.41.2.8 SQL-diagnostics statements.137
4.41.2.9 SQL-dynamicstatements.137
4.41.2.10 SQLembeddedexceptiondeclaration.137
4.41.3 SQL-statementsandSQL-dataaccessindication.137
4.41.4 SQL-statementsandtransactionstates.138
4.41.5 SQL-statementatomicityandstatementexecutioncontexts.140
4.41.6 EmbeddableSQL-statements.141
4.41.7 PreparableandimmediatelyexecutableSQL-statements.142
4.41.8 DirectlyexecutableSQL-statements.144
4.42 Basicsecuritymodel.145
4.42.1 Authorization identifiers.145
4.42.1.1 Introductiontoauthorizationidentifiers.145
4.42.1.2 SQL-sessionauthorizationidentifiers.145
4.42.1.3 SQL-clientmoduleauthorizationidentifiers.146
4.42.1.4 SQL-schemaauthorizationidentifiers.146
4.42.2 Privileges.146
4.42.3 Roles.149
4.42.4 Securitymodeldefinitions.149
©ISO/IEC2023–Allrightsreserved vii
---------------------- Page: 7 ----------------------
ISO/IEC9075-2:2023(E)
4.43 SQL-transactions.149
4.43.1 GeneraldescriptionofSQL-transactions.149
4.43.2 Savepoints.150
4.43.3 PropertiesofSQL-transactions.151
4.43.4 IsolationlevelsofSQL-transactions.151
4.43.5 Implicit rollbacks.152
4.43.6 EffectsofSQL-statementsinanSQL-transaction.153
4.43.7 Encompassingtransactions.153
4.43.7.1 Encompassingtransactionbelongingtoanexternalagent.153
4.43.7.2 EncompassingtransactionbelongingtotheSQL-agent.153
4.44 SQL-connections.154
4.45 SQL-sessions.155
4.45.1 GeneraldescriptionofSQL-sessions.155
4.45.2 SQL-sessionidentification.156
4.45.3 SQL-session properties.156
4.45.4 SQL-sessioncontextmanagement.159
4.45.5 Execution contexts.159
4.45.6 Routineexecutioncontext.159
4.46 Triggers.160
4.46.1 Generaldescriptionoftriggers.160
4.46.2 Trigger execution.161
4.47 Client-server operation.163
4.48 JSONdatahandlinginSQL.163
4.48.1 Introduction.163
4.48.2 ImpliedJSONdatamodel.164
4.48.3 SQL/JSONdatamodel.165
4.48.4 SQL/JSON functions.166
4.48.5 OverviewofSQL/JSONpathlanguage.167
5 Lexical elements.169
5.1 .169
5.2 and.173
5.3 .183
5.4 Namesandidentifiers.194
6 Scalar expressions.206
6.1 .206
6.2 .219
6.3 .221
6.4 and.223
6.5 .228
6.6 .230
6.7 .234
6.8 .237
6.9 .238
6.10 .241
6.11 .247
6.12 .250
6.13 .254
viii ©ISO/IEC2023–Allrightsreserved
---------------------- Page: 8 ----------------------
ISO/IEC9075-2:2023(E)
6.14 .271
6.15 .273
6.16 .275
6.17 .276
6.18 .278
6.19 .280
6.20 .282
6.21 .284
6.22 .285
6.23 .286
6.24 .288
6.25 .290
6.26 .291
6.27 .292
6.28 .296
6.29 .298
6.30 .300
6.31 .302
6.32 .316
6.33 .321
6.34 .341
6.35 .348
6.36 .
...
Questions, Comments and Discussion
Ask us and Technical Secretary will try to provide an answer. You can facilitate discussion about the standard in here.