ISO/IEC 9075-2:2023

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

• Status: Published
• Publication Date: 31-May-2023
• ICS: 35.060 - Languages used in information technology
• Technical Committee: ISO/IEC JTC 1/SC 32 - Data management and interchange
• Drafting Committee: ISO/IEC JTC 1/SC 32/WG 3 - Database language
• Current Stage: 9092 - International Standard to be revised
• Start Date: 22-Jun-2025
• Completion Date: 30-Oct-2025

Overview - ISO/IEC 9075-2:2023 (SQL / Foundation)

ISO/IEC 9075-2:2023, Information technology - Database languages SQL - Part 2: Foundation (SQL/Foundation), is the ISO/IEC standard that defines the foundational language elements, data models and core semantics of SQL. The sixth edition (2023) specifies the baseline SQL language constructs, basic data types, operations and behaviors that ensure consistent SQL implementation and interoperability across database systems.

Key topics and technical scope

This part of the SQL standard covers the essential building blocks and behavioral rules for SQL implementations, including:

• Data types and literals: character strings, binary strings, numeric types, Boolean, datetimes, intervals, JSON types, user-defined types, row and collection types (arrays, multisets).
• Character handling and collations: repertoires, encoding forms, comparison and operations on character strings.
• Data conversions and domains: implicit/explicit conversions and domain semantics.
• Table and row models: base tables (including system-versioned and temporary tables), derived and transient tables, columns (base and generated), identity columns, table updatability and unique identification.
• Query and expression semantics: scalar expressions, table expressions, query specifications and query expression composition.
• Data analysis and windowing: group functions, window functions, aggregate functions and advanced analytical features.
• Row pattern matching: pattern definitions, partitioning, measures and row navigation semantics.
• Integrity and constraint rules: unique constraints, referential constraints, check constraints, domain constraints and assertions.
• Functional dependencies and candidate keys: formal rules for dependencies and key identification within queries and tables.
• Routines, invocation and dynamic SQL: SQL-invoked routines, externally-invoked procedures, direct invocation, descriptors, result sets and dynamic SQL concepts.
• Additional foundations: periods, collections, sequence generators, host parameters, SQL-paths and determinism rules.

Practical applications - who uses this standard

ISO/IEC 9075-2 is essential for:

• DBMS implementers and database engine vendors building SQL-compliant systems.
• Database architects and developers ensuring portability of SQL code across platforms.
• Tool and library authors (ORMs, SQL parsers, analysis tools) who need authoritative SQL semantics.
• Compliance and QA teams validating vendor claims of SQL conformance.
• Educators and technical authors teaching SQL language principles and standards-based features.

Adopting SQL/Foundation helps ensure predictable behavior for queries, consistent handling of data types (including JSON and temporal types), reliable constraint enforcement and portability of analytics and windowed queries.

Related standards

ISO/IEC 9075-2:2023 is part of the broader ISO/IEC 9075 (SQL) series. Implementers should consult other parts of the SQL standard series to cover language modules beyond the foundation (such as procedural modules, call-level interfaces and extensions).

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 2023
© 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: [phone]
Email: [e-mail]
Website: www.iso.org
Published in Switzerland
ii
© ISO/IEC 2023 – All rights reserved

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

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

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

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

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

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

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 .351
6.37 .354
6.38 .355
6.39 .356
6.40 .357
6.41 .359
6.42 .360
6.43 .363
6.44 .365
6.45 .369
6.46 .370
6.47 .374
6.48 .376
6.49 .378
6.50 .380
6.51 .383
6.52 .384
7 Query expressions.386
7.1 .386
7.2 .389
7.3 .391
7.4 .393
7.5 .394
7.6 .397
7.7 .415
©ISO/IEC2023–Allrightsreserved ix

ISO/IEC9075-2:2023(E)
7.8 .420
7.9 .422
7.10 .427
7.11 .437
7.12 .451
7.13 .452
7.14 .461
7.15 .463
7.16 .478
7.17 .486
7.18 .503
7.19 .507
8 Predicates.509
8.1 .509
8.2 .511
8.3 .519
8.4 .520
8.5 .522
8.6 .528
8.7 .534
8.8 .536
8.9 .538
8.10 .540
8.11 .541
8.12 .543
8.13 .545
8.14 .548
8.15 .550
8.16 .553
8.17 .555
8.18 .557
8.19 .558
8.20 .560
8.21 .565
8.22 .566
8.23 .568
9 Additionalcommonrules.570
9.1 Retrieval assignment.570
9.2 Store assignment.576
9.3 PassingavaluefromahostlanguagetotheSQL-server.582
9.4 PassingavaluefromtheSQL-servertoahostlanguage.586
9.5 Resultofdatatypecombinations.590
9.6 Subjectroutinedetermination.594
9.7 Typeprecedencelistdetermination.596
9.8 Hostparametermodedetermination.600
9.9 Typenamedetermination.602
9.10 Determinationofidenticalvalues.604
x ©ISO/IEC2023–Allrightsreserved

ISO/IEC9075-2:2023(E)
9.11 Equality operations.606
9.12 Groupingoperations.608
9.13 Multisetelementgroupingoperations.610
9.14 Orderingoperations.612
9.15 Collationdetermination.614
9.16 Potentialsourcesofnon-determinism.616
9.17 Executingan.620
9.18 InvokinganSQL-invokedroutine.625
9.19 Processingamethodinvocation.655
9.20 Transformationofqueryspecifications.657
9.21 Executionofarray-returningexternalfunctions.660
9.22 Executionofmultiset-returningexternalfunctions.664
9.23 Evaluationandtransformationof.665
9.24 Compilationofaninvocationofapolymorphictablefunction.669
9.25 Executionofaninvocationofapolymorphictablefunction.674
9.26 SignaturesofPTFcomponentprocedures.685
9.27 InvocationofaPTFcomponentprocedure.688
9.28 XQueryregularexpressionmatching.691
9.29 XQueryregularexpressionreplacement.694
9.30 Datatypeidentity.696
9.31 Determinationofafrom-sqlfunction.698
9.32 Determinationofafrom-sqlfunctionforanoverridingmethod.699
9.33 Determinationofato-sqlfunction.700
9.34 Determinationofato-sqlfunctionforanoverridingmethod.701
9.35 Generationofthenextvalueofasequencegenerator.702
9.36 Creationofasequencegenerator.704
9.37 Alteringasequencegenerator.707
9.38 Generationofthehierarchicalofaview.710
9.39 Determinationofviewprivileges.711
9.40 Determinationofviewcomponentprivileges.713
9.41 Rowpatternrecognitioninasequenceofrows.717
9.42 ParsingJSONtext.721
9.43 SerializinganSQL/JSONitem.724
9.44 ConvertinganSQL/JSONsequencetoanSQL/JSONitem.726
9.45 SQL/JSONpathlanguage:lexicalelements.729
9.46 SQL/JSONpathlanguage:syntaxandsemantics.733
9.47 Processing.764
9.48 CastinganSQL/JSONsequencetoanSQLtype.765
9.49 SerializinganSQL/JSONsequencetoanSQLstringtype.768
9.50 Convertingadatetimetoaformattedcharacterstring.772
9.51 Convertingaformattedcharacterstringtoadatetime.776
9.52 Datetimetemplates.784
10 Additionalcommonelements.788
10.1 .788
10.2 .792
10.3 .794
10.4 .795
©ISO/IEC2023–Allrightsreserved xi

ISO/IEC9075-2:2023(E)
10.5 .799
10.6 .801
10.7 .804
10.8 and.805
10.9 .807
10.10 .823
10.11 .825
10.12 .831
10.13 .833
10.14 .835
11 Schemadefinitionandmanipulation.837
11.1 .837
11.2 .840
11.3 .843
11.4 .855
11.5 .861
11.6 .865
11.7 .867
11.8 .870
11.9 .876
11.10 .878
11.11 .879
11.12 .881
11.13 .883
11.14 .884
11.15 .885
11.16 .886
11.17 .887
11.18 .888
11.19 .890
11.20 .894
11.21 .896
11.22 .897
11.23 .898
11.24 .900
11.25 .901
11.26 .902
11.27 .905
11.28 ...