# Overview of SQL in AWS Clean Rooms
<a name="sql-reference"></a>

Welcome to the *AWS Clean Rooms SQL Reference.*

AWS Clean Rooms is built around industry-standard Structured Query Language (SQL), a query language that consists of commands and functions that you use to work with databases and database objects. SQL also enforces rules regarding the use of data types, expressions, and literals.

The following topics provide general information about the conventions and naming rules used in this SQL Reference.

**Topics**
+ [SQL reference conventions](sql-ref-conventions.md)
+ [SQL naming rules](sql-ref-naming.md)
+ [Data type support by SQL engine](sql-reference-supported-data-types-dialect.md)

The following sections provide information about the literals, data types, SQL commands, types of SQL functions, and SQL conditions you can use in AWS Clean Rooms.
+ [AWS Clean Rooms Spark SQL](sql-reference-spark.md)

For more information about AWS Clean Rooms, see the [AWS Clean Rooms User Guide](https://docs.aws.amazon.com/clean-rooms/latest/userguide/what-is.html) and the [AWS Clean Rooms API Reference](https://docs.aws.amazon.com/clean-rooms/latest/apireference/Welcome.html).

# SQL reference conventions
<a name="sql-ref-conventions"></a>

This section explains the conventions that are used to write the syntax for the SQL expressions, commands, and functions.


| Character | Description | 
| --- | --- | 
| CAPS | Words in capital letters are key words. | 
| [ ]  | Brackets denote optional arguments. Multiple arguments in brackets indicate that you can choose any number of the arguments. In addition, arguments in brackets on separate lines indicate that the parser expects the arguments to be in the order that they are listed in the syntax. | 
| \$1 \$1  | Braces indicate that you are required to choose one of the arguments inside the braces. | 
| \$1 | Pipes indicate that you can choose between the arguments. | 
| italics | Words in italics indicate placeholders. You must insert the appropriate value in place of the word in italics. | 
| ... | An ellipsis indicates that you can repeat the preceding element. | 
| '  | Words in single quotation marks indicate that you must type the quotes. | 

# SQL naming rules
<a name="sql-ref-naming"></a>

The following sections explain the SQL naming rules in AWS Clean Rooms.

**Topics**
+ [Configured table association names and columns](#confg-table-and-column-naming-rules)
+ [Reserved words](#reserved-words)

## Configured table association names and columns
<a name="confg-table-and-column-naming-rules"></a>

Members who can query use configured table association names as table names in queries. Configured table association names and configured table columns can be aliased in queries. 

The following naming rules apply to configured table association names, configured table column names, and aliases: 
+ They must use only alphanumeric, underscore (\$1), or hyphen (-) characters but can't start or end with a hyphen.
  + (*Custom analysis rule only*) They can use the dollar sign (\$1) but can't use a pattern that follows a dollar-quoted string constant. 

    A dollar-quoted string constant consists of:
    + a dollar sign (\$1)
    + an optional "tag" of zero or more characters
    + another dollar sign
    + arbitrary sequence of characters that makes up the string content
    + a dollar sign (\$1)
    + the same tag that began the dollar quote
    + a dollar sign

      For example: `$$invalid$$`
+ They can't contain consecutive hyphen (-) characters.
+ They can't begin with any of the following prefixes: 

  `padb_`, `pg_`, `stcs_`, `stl_`, `stll_`, `stv_`, `svcs_`, `svl_`, `svv_`, `sys_`, `systable_`
+ They can't contain backslash characters (\$1) , quotation marks ('), or spaces that aren't double-quoted.
+ If they start with a non-alphabetical character, they must be within double-quotes (" ").
+ If they contain a hyphen (-) character, they must be within double-quotes (" ").
+ They must be between 1 and 127 characters in length.
+ [Reserved words](#reserved-words) must be within double-quotes (" ").
+ The following column names are reserved can't be used in AWS Clean Rooms (even with quotes):
  + oid
  + tableoid
  + xmin
  + cmin
  + xmax
  + cmax
  + ctid

## Reserved words
<a name="reserved-words"></a>

The following is a list of reserved words in AWS Clean Rooms.


|  |  |  |  | 
| --- |--- |--- |--- |
|  AES128  |  DELTA32KDESC  |  LEADING  |  PRIMARY  | 
|  AES256ALL  |  DISTINCT  |  LEFTLIKE  |  RAW  | 
|  ALLOWOVERWRITEANALYSE  |  DO  |  LIMIT  |  READRATIO  | 
|  ANALYZE  |  DISABLE  |  LOCALTIME  |  RECOVERREFERENCES  | 
|  AND  |  ELSE  |  LOCALTIMESTAMP  |  REJECTLOG  | 
|  ANY  |  EMPTYASNULLENABLE  |  LUN  |  RESORT  | 
|  ARRAY  |  ENCODE  |  LUNS  |  RESPECT  | 
|  AS  |  ENCRYPT   |  LZO  |  RESTORE  | 
|  ASC  |  ENCRYPTIONEND  |  LZOP  |  RIGHTSELECT  | 
|  AUTHORIZATION  |  EXCEPT  |  MINUS  |  SESSION\$1USER  | 
|  AZ64  |  EXPLICITFALSE  |  MOSTLY16  |  SIMILAR  | 
|  BACKUPBETWEEN  |  FOR  |  MOSTLY32  |  SNAPSHOT  | 
|  BINARY  |  FOREIGN  |  MOSTLY8NATURAL  |  SOME  | 
|  BLANKSASNULLBOTH  |  FREEZE  |  NEW  |  SYSDATESYSTEM  | 
|  BYTEDICT  |  FROM  |  NOT  |  TABLE  | 
|  BZIP2CASE  |  FULL  |  NOTNULL  |  TAG  | 
|  CAST  |  GLOBALDICT256  |  NULL  |  TDES  | 
|  CHECK  |  GLOBALDICT64KGRANT  |  NULLSOFF  |  TEXT255  | 
|  COLLATE  |  GROUP  |  OFFLINEOFFSET  |  TEXT32KTHEN  | 
|  COLUMN  |  GZIPHAVING  |  OID  |  TIMESTAMP  | 
|  CONSTRAINT  |  IDENTITY  |  OLD  |  TO  | 
|  CREATE  |  IGNOREILIKE  |  ON  |  TOPTRAILING  | 
|  CREDENTIALSCROSS  |  IN  |  ONLY  |  TRUE   | 
|  CURRENT\$1DATE  |  INITIALLY  |  OPEN  |  TRUNCATECOLUMNSUNION  | 
|  CURRENT\$1TIME  |  INNER  |  OR  |  UNIQUE  | 
|  CURRENT\$1TIMESTAMP  |  INTERSECT  |  ORDER  | UNNEST | 
|  CURRENT\$1USER  |  INTERVAL  |  OUTER  |  USING  | 
|  CURRENT\$1USER\$1IDDEFAULT  |  INTO  |  OVERLAPS  |  VERBOSE  | 
|  DEFERRABLE  |  IS  |  PARALLELPARTITION  |  WALLETWHEN  | 
|  DEFLATE  |  ISNULL  |  PERCENT  |  WHERE  | 
|  DEFRAG  |  JOIN  |  PERMISSIONS  |  WITH  | 
|  DELTA  |  LANGUAGE  |  PIVOTPLACING  |  WITHOUT  | 

# Data type support by SQL engine
<a name="sql-reference-supported-data-types-dialect"></a>

AWS Clean Rooms supports multiple SQL engines and dialects. Understanding the data type systems across these implementations is crucial for successful data collaboration and analysis. The following tables show the equivalent data types across AWS Clean Rooms SQL, Snowflake SQL, and Spark SQL. 

## Numeric data types
<a name="numeric-data-types-table"></a>

Numeric types represent various kinds of numbers, from precise integers to approximate floating-point values. The choice of numeric type affects both storage requirements and computational precision. Integer types vary by byte size, while decimal and floating-point types offer different precision and scale options. 


| Data type | AWS Clean Rooms SQL | Snowflake SQL | Spark SQL | Description | 
| --- | --- | --- | --- | --- | 
| 8-byte Integer | BIGINT | Not supported | BIGINT, LONG | Signed integers from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. | 
| 4-byte Integer | INT | Not supported | INT, INTEGER | Signed integers from -2,147,483,648 to 2,147,483,647 | 
| 2-byte Integer | SMALLINT  | Not supported | SMALLINT, SHORT | Signed integers from -32,768 to 32,767 | 
| 1-byte Integer | Not supported | Not supported | TINYINT, BYTE | Signed integers from -128 to 127 | 
|  Double Precision Float | DOUBLE, DOUBLE PRECISION | FLOAT, FLOAT4, FLOAT8, DOUBLE, DOUBLE PRECISION, REAL | DOUBLE | 8-byte double-precision floating point numbers | 
| Single Precision Float | REAL, FLOAT | Not supported | FLOAT | 4-byte single-precision floating point numbers | 
| Decimal (fixed precision) | DECIMAL  | DECIMAL, NUMERIC, NUMBER Snowflake automatically aliases smaller-width exact numeric types (INT, BIGINT, SMALLINT, etc.) to NUMBER.  | DECIMAL, NUMERIC,  | Arbitrary-precision signed decimal numbers | 
| Decimal (with precision) | DECIMAL(p) | DECIMAL(p), NUMBER(p) | DECIMAL(p) | Fixed-precision decimal numbers | 
| Decimal (with scale) | DECIMAL(p,s) | DECIMAL(p,s), NUMBER(p,s) | DECIMAL(p,s) | Fixed-precision decimal numbers with scale | 

## Boolean data types
<a name="boolean-data-types-table"></a>

Boolean types represent simple true/false logical values. These types are consistent across SQL engines and are commonly used for flags, conditions, and logical operations. 


| Data type | AWS Clean Rooms SQL | Snowflake SQL | Spark SQL | Description | 
| --- | --- | --- | --- | --- | 
| Boolean  | BOOLEAN  | BOOLEAN  | BOOLEAN  | Represents true/false values  | 

## Date and time data types
<a name="date-time-data-types-table"></a>

Date and time types handle temporal data, with varying levels of precision and time zone awareness. These types support different formats for storing dates, times, and timestamps, with options for including or excluding time zone information. 


| Data type | AWS Clean Rooms SQL | Snowflake SQL | Spark SQL | Description | 
| --- | --- | --- | --- | --- | 
| Date  | DATE  | DATE  | DATE  | Date values (year, month, day) without time zone | 
| Time  | TIME  | Not supported | Not supported | Time of day in UTC, without time zone | 
| Time with TZ | TIMETZ  | Not supported | Not supported | Time of day in UTC, with time zone | 
| Timestamp  | TIMESTAMP  | TIMESTAMP, TIMESTAMP\$1NTZ | TIMESTAMP\$1NTZ |  Timestamp without time zone NTZ indicates "No Time Zone"  | 
| Timestamp with TZ | TIMESTAMPTZ  | TIMESTAMP\$1LTZ | TIMESTAMP, TIMESTAMP\$1LTZ | Timestamp with local time zone LTZ indicates "Local Time Zone"  | 

## Character data types
<a name="character-data-types-table"></a>

Character types store textual data, offering both fixed-length and variable-length options. These types handle text strings and binary data, with optional length specifications to control storage allocation. 


| Data type | AWS Clean Rooms SQL | Snowflake SQL | Spark SQL | Description | 
| --- | --- | --- | --- | --- | 
| Fixed-length Character  | CHAR  | CHAR, CHARACTER | CHAR, CHARACTER | Fixed-length character string | 
| Fixed-length Character with Length | CHAR(n) | CHAR(n), CHARACTER(n) | CHAR(n), CHARACTER(n) | Fixed-length character string with specified length | 
| Variable-length Character | VARCHAR  | VARCHAR, STRING, TEXT | VARCHAR, STRING | Variable-length character string | 
| Variable-length Character with Length | VARCHAR(n) | VARCHAR(n), STRING(n), TEXT(n) | VARCHAR(n) | Variable-length character string with length limit  | 
| Binary  | VARBYTE  | BINARY, VARBINARY | BINARY  | Binary byte sequence | 
| Binary with Length | VARBYTE(n) | Not supported | Not supported | Binary byte sequence with length limit | 

## Structured data types
<a name="structured-data-types-table"></a>

Structured types allow for complex data organization by combining multiple values into single fields. These include arrays for ordered collections, maps for key-value pairs, and structs for creating custom data structures with named fields. 


| Data type | AWS Clean Rooms SQL | Snowflake SQL | Spark SQL | Description | 
| --- | --- | --- | --- | --- | 
| Array  | ARRAY<type> | ARRAY(type) | ARRAY<type> | Ordered sequence of elements of the same type Array types must contain elements of the same type  | 
| Map  | MAP<key,value> | MAP(key,value) | MAP<key,value> | Collection of key-value pairs Map types must contain elements of the same type  | 
| Struct  | STRUCT< field1: type1, field2: type2> |  OBJECT( field1 type1, field2 type2 ) | STRUCT< field1: type1, field2: type2 > | Structure with named fields of specified types Structured type syntax may vary slightly between implementations  | 
| Super  | SUPER  | Not supported | Not supported | Flexible type supporting all data types including complex types |