# SQL Keywords and Operators in ksqlDB for Confluent Platform This reference lists all valid keywords and operators in ksqlDB SQL. ## Keywords The following table shows all keywords in the language. #### SQL Keywords | Keyword | Description | Example | |--------------|----------------------------------------------------------------|----------------------------------------------------------------------| | ADD | Add a column to a stream or table | ADD (COLUMN)? identifier type; | | ADVANCE BY | Hop size in hopping window | WINDOW HOPPING (SIZE 30 SECONDS, ADVANCE BY 10 SECONDS) | | ALL | List hidden topics | SHOW ALL TOPICS; | | ALTER | Change a property, stream, or table | ALTER ‘auto.offset.reset’=’latest’; | | AND | Logical “and” operator | WHERE userid<>’User_1’ AND userid<>’User_2’ | | ARRAY | One-indexed array of elements | SELECT ARRAY[1, 2] FROM s1 EMIT CHANGES; | | AS | Alias a column, expression, or type | No Example Provided | | ASSERT | Assert value, stream, table, tombstone, topic, schema | ASSERT NULL VALUES sourceName (columns)? KEY values; | | BEGINNING | Print from start of topic | PRINT FROM BEGINNING; | | BETWEEN | Constrain a value to a range | SELECT event FROM events WHERE event_id BETWEEN 10 AND 20 … | | BY | Specify expression | GROUP BY regionid, ADVANCE BY 10 SECONDS, PARTITION BY userid | | BYTES | Bytes data type | No Example Provided | | CASE | Select a condition from expressions | SELECT CASE WHEN condition THEN result [ WHEN … THEN … ] … END | | CAST | Change expression type | SELECT id, CONCAT(CAST(COUNT(\*) AS VARCHAR), ‘_HELLO’) FROM views … | | CHANGES | Specify incremental refinement type | SELECT \* FROM users EMIT CHANGES; | | CONNECTOR | Manage a connector | CREATE SOURCE CONNECTOR ‘jdbc-connector’ WITH( … | | CONNECTORS | List all connectors | SHOW CONNECTORS; | | CREATE | Create an object | CREATE STREAM rock_songs (artist VARCHAR, title VARCHAR) … | | DATE | Date data type | No Example Provided | | DAY | Time unit of one day for a window | WINDOW TUMBLING (SIZE 30 SECONDS, RETENTION 1 DAY) | | DAYS | Time unit of days for a window | WINDOW TUMBLING (SIZE 30 SECONDS, RETENTION 1000 DAYS) | | DECIMAL | Decimal numeric type | No Example Provided | | DEFINE | Define a variable | DEFINE name = ‘Tom Sawyer’; | | DELETE | Remove a topic | DROP TABLE DELETE TOPIC; | | DESCRIBE | List details for object(s) | DESCRIBE PAGEVIEWS; | | DISTINCT | Test for uniqueness | CREATE STREAM output AS SELECT A, A IS NOT DISTINCT FROM 1, …); | | DROP | Delete an object | DROP CONNECTOR ; | | ELSE | Condition in WHEN statement | CASE WHEN units<2 THEN ‘sm’ WHEN units<4 THEN ‘med’ ELSE ‘large’ … | | EMIT | Specify push query | SELECT \* FROM users EMIT CHANGES; | | END | Close a CASE block | SELECT CASE WHEN condition THEN result [ WHEN … THEN … ] … END | | EXISTS | Test whether object exists | DROP STREAM IF EXISTS ; | | EXPLAIN | Show execution plan | EXPLAIN ; or EXPLAIN ; | | EXTENDED | List details for object(s) | DESCRIBE EXTENDED; | | FALSE | Boolean value of false | No Example Provided | | FINAL | Suppress intermediate results on a windowed aggregation | SELECT \* FROM users EMIT FINAL; | | FROM | Specify record source for queries | SELECT \* FROM users; | | FULL | Specify FULL JOIN | CREATE TABLE t AS SELECT \* FROM l FULL OUTER JOIN r ON l.ID = r.ID; | | FUNCTION | List details for a function | DESCRIBE FUNCTION ; | | FUNCTIONS | List all functions | SHOW FUNCTIONS; | | GRACE PERIOD | Grace period for a tumbling window | WINDOW TUMBLING (SIZE 1 HOUR, GRACE PERIOD 2 HOURS) | | GROUP | Group rows with the same values | SELECT regionid, COUNT(\*) FROM pageviews GROUP BY regionid | | HAVING | Condition expression | GROUP BY card_number HAVING COUNT(\*) > 3 | | HEADER | Specify a header column | CREATE STREAM S (H BYTES HEADER(‘abc’)…) | | HEADERS | Specify a headers column | CREATE STREAM S (H ARRAY> HEADERS…) | | HOPPING | Specify a hopping window | WINDOW HOPPING (SIZE 30 SECONDS, ADVANCE BY 10 SECONDS) | | HOUR | Time unit of one hour for a window | WINDOW TUMBLING (SIZE 1 HOUR, RETENTION 1 DAY) | | HOURS | Time unit of hours for a window | WINDOW TUMBLING (SIZE 2 HOURS, RETENTION 1 DAY) | | IF EXISTS | Test whether object exists | DROP STREAM IF EXISTS ; | | IN | Specify multiple values | WHERE name IN (value1, value2, …) | | INNER JOIN | Specify INNER JOIN | CREATE TABLE t AS SELECT \* FROM l INNER JOIN r ON l.ID = r.ID; | | INSERT | Insert new records in a stream/table | INSERT INTO … | | INTEGER | Integer numeric type | CREATE TABLE profiles (id INTEGER PRIMARY KEY, …) | | INTERVAL | Number of messages to skip in PRINT | PRINT INTERVAL 5; | | INTO | Stream/table to insert values | INSERT INTO stream_name … | | IS | Use with keywords like NOT and DISTINCT | CREATE STREAM output AS SELECT A, A IS NOT DISTINCT FROM 1, …); | | JOIN | Match records in streams/tables | CREATE TABLE t AS SELECT \* FROM l INNER JOIN r ON l.ID = r.ID; | | KEY | Specify key column | CREATE TABLE users (userId INTEGER PRIMARY KEY, …) | | LEFT JOIN | Specify LEFT JOIN | CREATE TABLE t AS SELECT \* FROM l LEFT JOIN r ON l.ID = r.ID; | | LIKE | Match pattern | WHERE UCASE(gender)=’FEMALE’ AND LCASE(regionid) LIKE ‘%_6’ | | LIMIT | Number of records to output | SELECT \* FROM users EMIT CHANGES LIMIT 5; | | LIST | List objects | LIST STREAMS; or SHOW STREAMS; | | MAP | map data type | SELECT MAP(k1:=v1, k2:=v1\*2) FROM s1 EMIT CHANGES; | | MILLISECOND | Time unit of one ms for a window | WINDOW TUMBLING (SIZE 1 MILLISECOND, RETENTION 1 DAY) | | MILLISECONDS | Time unit of ms for a window | WINDOW TUMBLING (SIZE 100 MILLISECONDS, RETENTION 1 DAY) | | MINUTE | Time unit of one min for a window | WINDOW TUMBLING (SIZE 1 MINUTE, RETENTION 1 DAY) | | MINUTES | Time unit of mins for a window | WINDOW TUMBLING (SIZE 30 MINUTES, RETENTION 1 DAY) | | MONTH | Time unit of one month for a window | WINDOW TUMBLING (SIZE 1 HOUR, RETENTION 1 MONTH) | | MONTHS | Time unit of months for a window | WINDOW TUMBLING (SIZE 1 HOUR, RETENTION 2 MONTHS) | | NOT | Logical “not” operator | (no example provided) | | NULL | Field with no value | CREATE STREAM s AS SELECT \* FROM t WHERE (t.c1 IS NOT NULL); | | ON | Specify join criteria | LEFT JOIN users ON pageviews.userid = users.userid | | OR | Logical “or” operator | WHERE userid=’User_1’ OR userid=’User_2’ | | OUTER JOIN | Specify OUTER JOIN | CREATE TABLE t AS SELECT \* FROM l FULL OUTER JOIN r ON l.ID = r.ID; | | PARTITION BY | Repartition a stream | PARTITION BY | | PARTITIONS | Partitions to distribute keys over | CREATE STREAM users_rekeyed WITH (PARTITIONS=6) AS … | | PERIOD | Grace period for a tumbling window | WINDOW TUMBLING (SIZE 1 HOUR, GRACE PERIOD 2 HOURS) | | PLUGINS | Show connector plugins | LIST CONNECTOR PLUGINS; or SHOW CONNECTOR PLUGINS; | | PRIMARY KEY | Specify primary key column | CREATE TABLE users (userId INTEGER PRIMARY KEY, …) | | PRINT | Output records in a topic | PRINT FROM BEGINNING; | | PROPERTIES | List all properties | LIST PROPERTIES; or SHOW PROPERTIES; | | PAUSE | Pause a persistent query | PAUSE query_id; | | QUERIES | List all queries | LIST QUERIES; or SHOW QUERIES; | | REPLACE | String replace | REPLACE(col1, ‘foo’, ‘bar’) | | RESUME | Resume a paused persistent query | RESUME query_id; | | RETENTION | Time to retain past windows | WINDOW TUMBLING (SIZE 30 SECONDS, RETENTION 1000 DAYS) | | RIGHT JOIN | Specify RIGHT JOIN | CREATE TABLE t AS SELECT \* FROM l RIGHT JOIN r ON l.ID = r.ID; | | RUN SCRIPT | Execute queries from a file | RUN SCRIPT ; | | SAMPLE | Number of messages to skip in PRINT | PRINT SAMPLE 5; | | SCHEMA | Specify a schema | ASSERT SCHEMA SUBJECT ‘foo-value’ ID 5; | | SECOND | Time unit of one sec for a window | WINDOW TUMBLING (SIZE 1 SECOND, RETENTION 1 DAY) | | SECONDS | Time unit of secs for a window | WINDOW TUMBLING (SIZE 30 SECONDS, RETENTION 1 DAY) | | SELECT | Query a stream or table | SELECT \* FROM metrics EMIT CHANGES; | | SESSION | Specify a session window | WINDOW SESSION (60 SECONDS) | | SET | Assign a property value | SET ‘auto.offset.reset’=’earliest’; | | SHOW | List objects | SHOW FUNCTIONS; | | SINK | Create a sink connector | CREATE SINK CONNECTOR … | | SIZE | Time length of a window | WINDOW TUMBLING (SIZE 5 SECONDS) | | SOURCE | Create a source connector | CREATE SOURCE CONNECTOR … | | STREAM | Register a stream on a topic | CREATE STREAM users_orig AS SELECT \* FROM users EMIT CHANGES; | | STREAMS | List all streams | SHOW STREAMS; | | STRUCT | Struct data type | SELECT STRUCT(f1 := v1, f2 := v2) FROM s1 EMIT CHANGES; | | TABLE | Register a table on a topic | CREATE TABLE users (id BIGINT PRIMARY KEY, …) | | TABLES | List all tables | SHOW TABLES; | | TERMINATE | End a persistent query | TERMINATE query_id; | | THEN | Return expression in a CASE block | CASE WHEN units<2 THEN ‘sm’ WHEN units<4 THEN ‘med’ ELSE ‘large’ … | | TIME | Time data type | No Example Provided | | TIMEOUT | Specify the amount of time to wait for an assertion to succeed | ASSERT SCHEMA ID 4 TIMEOUT 10 SECONDS; | | TIMESTAMP | Timestamp data type | No Example Provided | | TIMESTAMP | Specify a timestamp column | CREATE STREAM pageviews WITH (TIMESTAMP=’viewtime’, … | | TOPIC | Specify a topic | DROP TABLE DELETE TOPIC; | | TOPICS | List all topics | LIST TOPICS; or SHOW TOPICS; | | TRUE | Boolean value of true | No Example Provided | | TUMBLING | Specify a tumbling window | WINDOW TUMBLING (SIZE 5 SECONDS) | | TYPE | Alias a complex type | CREATE TYPE AS ; | | TYPES | List all custom type aliases | LIST TYPES; or SHOW TYPES; | | UNDEFINE | Undefine a variable | UNDEFINE name; | | UNSET | Unassign a property value | UNSET ‘auto.offset.reset’; | | VALUES | List of values to insert | INSERT INTO foo VALUES (‘key’, ‘A’); | | VARIABLES | List all variables | LIST VARIABLES; or SHOW VARIABLES; | | WHEN | Specify condition in a CASE block | SELECT CASE WHEN condition THEN result [ WHEN … THEN … ] … END | | WHERE | Filter records by a condition | SELECT \* FROM pageviews WHERE pageid < ‘Page_20’ | | WINDOW | Groups rows with the same keys | SELECT userid, COUNT(\*) FROM users WINDOW SESSION (60 SECONDS) … | | WITH | Specify object creation params | CREATE STREAM pageviews WITH (TIMESTAMP=’viewtime’, … | | WITHIN | Time range in a windowed join | SELECT \* FROM impressions i JOIN clicks c WITHIN 1 minute … | | YEAR | Time unit of one year for a window | WINDOW TUMBLING (SIZE 1 HOUR, RETENTION 1 YEAR) | | YEARS | Time unit of years for a window | WINDOW TUMBLING (SIZE 1 HOUR, RETENTION 2 YEARS) | ## Operators The following table shows all operators in the language. | operator | meaning | applies to | |--------------|------------------------------------------------|-----------------| | `=` | is equal to | string, numeric | | `!=` or `<>` | is not equal to | string, numeric | | `<` | is less than | string, numeric | | `<=` | is less than or equal to | string, numeric | | `>` | is greater than | string, numeric | | `>=` | is greater than or equal to | string, numeric | | `+` | addition for numeric, concatenation for string | string, numeric | | `-` | subtraction | numeric | | `*` | multiplication | numeric | | `/` | division | numeric | | `%` | modulus | numeric | | `||` or `+` | concatenation | string | | `:=` | assignment | all | | `->` | struct field dereference | struct | | `.` | source dereference | table, stream | | `=>` | lambda expression | function | | `E` or `e` | exponent | numeric | | `NOT` | logical NOT | boolean | | `AND` | logical AND | boolean | | `OR` | logical OR | boolean | | `BETWEEN` | test if value within range | numeric, string | | `LIKE` | match a pattern | string | | `IN` | short hand for multiple `OR` expressions | all | | `--` | simple comment | string | | `/* */` | bracketed comment | string | | `--@` | directive comment | string |