# ZCQL -------------------------------------------------------------------------------- title: "Introduction" description: "ZCQL is Catalyst's own query language modelled after familiar query languages that you can use to query your app's database efficiently." last_updated: "2026-03-18T07:41:08.534Z" source: "https://docs.catalyst.zoho.com/en/cloud-scale/help/zcql/introduction/" service: "Cloud Scale" -------------------------------------------------------------------------------- # ZCQL ## Introduction Catalyst Cloud Scale ZCQL serves as Catalyst’s own query language, similar to {{%link href="https://www.mysql.com/" %}}MySQL{{%/link%}} or {{%link href="https://www.postgresql.org/" %}}PostgreSQL{{%/link%}}. It is used to perform data manipulations within the {{%link href="/en/cloud-scale/help/data-store/introduction/" %}}Catalyst Data Store{{%/link%}}. As a query engine powered by Catalyst, ZCQL enables developers to work with data using familiar SQL-like syntax while handling complex queries with high efficiency and scalability. You can perform the following operations on the tables in your Data Store using ZCQL: * {{%bold%}}Retrieval:{{%/bold%}} Obtaining records from existing tables * {{%bold%}}Insertion:{{%/bold%}} Inserting records into the existing columns of an existing table * {{%bold%}}Updating:{{%/bold%}} Updating the value of existing columns in an existing table * {{%bold%}}Deletion:{{%/bold%}} Deleting records from an existing table You can execute a variety of DML queries using ZCQL to obtain or manipulate data based on specific criteria by using various clauses and statements such as the SQL {{%badge%}}Join{{%/badge%}} clauses, {{%badge%}}Groupby{{%/badge%}} statements, {{%badge%}}OrderBy{{%/badge%}} statements, and the {{%badge%}}WHERE{{%/badge%}} and {{%badge%}}HAVING{{%/badge%}} clauses. ZCQL also supports several built-in SQL numeric functions that help you execute arithmetic operations easily. You can implement ZCQL queries in your Catalyst application's source code, such as in the functions or the client component. For example, you pass a ZCQL query in a function's body to retrieve data from a table in the Data Store and process it further. The Catalyst console also provides a ZCQL query execution window, where you can execute queries and view the responses. This allows you to test ZCQL queries easily, before implementing them in your application. ### SDK and API documentation Catalyst offers ZCQL in the Java, Node.js, Python, Web, Android, iOS, and Flutter SDK packages, and as an API. For code samples on executing ZCQL queries in these programming environments, refer to these help pages: * {{%link href="/en/sdk/java/v1/cloud-scale/zcql/execute-zcql-query/" %}}Execute ZCQL Query - Java SDK{{%/link%}} * {{%link href="/en/sdk/nodejs/v2/cloud-scale/zcql/execute-zcql-query/" %}}Execute ZCQL Query - Node.js SDK{{%/link%}} * {{%link href="/en/sdk/python/v1/cloud-scale/zcql/execute-zcql-query/" %}}Execute ZCQL Query - Python SDK{{%/link%}} * {{%link href="/en/sdk/web/v4/cloud-scale/zcql/execute-zcql-query/" %}}Execute ZCQL Query - Web SDK{{%/link%}} * {{%link href="/en/sdk/android/v2/cloud-scale/zcql/execute-zcql-query/" %}}Execute ZCQL Query - Android SDK{{%/link%}} * {{%link href="/en/sdk/ios/v2/cloud-scale/zcql/execute-zcql-query/" %}}Execute ZCQL Query - iOS SDK{{%/link%}} * {{%link href="/en/sdk/flutter/v2/cloud-scale/zcql/execute-zcql-query/" %}}Execute ZCQL Query - Flutter SDK{{%/link%}} * {{%link href="/en/api/code-reference/cloud-scale/zcql/execute-zcql-query/#ExecuteZCQLQuery" %}}Execute ZCQL Query - API{{%/link%}} {{%note%}}{{%bold%}}Note:{{%/bold%}} * From {{%bold%}}December 01st, 2024{{%/bold%}}, all your current projects in all your {{%link href="/en/getting-started/catalyst-organizations/" %}}Orgs{{%/link%}} present in the {{%link href="/en/deployment-and-billing/environments/development-environment/" %}}Development Environment{{%/link%}} will be automatically mapped to {{%bold%}}ZCQL V2 Parser{{%/bold%}}. * From {{%bold%}}April 01st, 2025{{%/bold%}}, all the projects present in all {{%italics%}}Orgs{{%/italics%}} that have already been mapped to {{%bold%}}ZCQL V2 Parser{{%/bold%}} in {{%italics%}}Development Environment{{%/italics%}} will be automatically mapped to {{%bold%}}ZCQL V2 Parser{{%/bold%}} in the {{%link href="/en/deployment-and-billing/environments/production-environment/" %}}Production Environment{{%/link%}}, if and when production is enabled for the project. * To use {{%bold%}}ZCQL V2{{%/bold%}} in your code, you need to set the appropriate environment variable as shown in this {{%link href=/en/cloud-scale/help/zcql/zcql-console/#using-zcql-v2-in-function-code" %}}help document{{%/link%}}.{{%/note%}} You can refer to {{%link href="/en/tutorials/" %}}Catalyst Tutorials{{%/link%}} to get an idea of ZCQL's implementation in the function or client code of the applications. ### Benefits * Can perform data retrieval, insertion, updating, and deletion operations in the Catalyst Data Store * Similarity to MySQL ensures ease of use and there is no separate learning curve * Can test the execution of the queries in the console before implementing them in the application * Can pass a ZCQL query in an API's body using the SDKs * Can perform arithmetic and numerical operations on the result set using the ZCQL built-in functions <br /> -------------------------------------------------------------------------------- title: "General Syntax of SELECT" description: "ZCQL is Catalyst's own query language modelled after familiar query languages that you can use to query your app's database efficiently." last_updated: "2026-03-18T07:41:08.534Z" source: "https://docs.catalyst.zoho.com/en/cloud-scale/help/zcql/select/" service: "Cloud Scale" -------------------------------------------------------------------------------- # SELECT ### General Syntax of SELECT ZCQL supports data retrieval query operations using the {{%badge%}}SELECT{{%/badge%}} command. This command lets you select a column or a set of columns from a base table and view the data records within the command's scope. The general syntax for a basic ZCQL data retrieval operation is as follows: {{%code class="language-sql"%}}SELECT {COLUMNS} FROM {BASE_TABLE_NAME} [JOIN_CLAUSE] [WHERE {WHERE_CONDITION}] [GROUP BY {GROUP_BY_COLUMN}] [ORDER BY {ORDER_BY_COLUMN}] LIMIT [{OFFSET}],{VALUE}{{%/code%}} <br /> {{%note%}}{{%bold%}}Note:{{%/bold%}}<br /> * The base table is the table you execute the query on. * You need not use the end-of-statement delimiter (;) at the end of a ZCQL query, while executing it in the ZCQL console. {{%/note%}} The base table is the table you execute the query on. We will discuss each ZCQL operation in detail with an example database and sample queries that you can execute on it. {{%bold%}}Example Database:{{%/bold%}} Imagine you're developing a ticket booking application where users can view the movie listings and showtimes for various theaters in a city, and can book movie tickets using the application. Let's create a table named '{{%bold%}}Movies{{%/bold%}}' that contains the showtime listings of the movies played in various theaters. Sample records from the _Movies_ table are given below: <table class="content-table"> <thead> <tr> <th><strong>MovieID</strong></th> <th><strong>MovieName</strong></th> <th><strong>ShowDate</strong></th> <th><strong>ShowTime</strong></th> <th><strong>TheaterID</strong></th> </tr> </thead> <tbody> <tr> <td>2056</td> <td>The First Purge</td> <td>2018-07-13</td> <td>13:00:00</td> <td>047</td> </tr> <tr> <td>2057</td> <td>Ant-Man and the Wasp</td> <td>2018-07-13</td> <td>14:20:00</td> <td>052</td> </tr> <tr> <td>2058</td> <td>Hotel Transylvania 3: Summer Vacation</td> <td>2018-07-14</td> <td>17:00:00</td> <td>052</td> </tr> <tr> <td>2059</td> <td>Skyscraper</td> <td>2018-07-14</td> <td>21:30:00</td> <td>053</td> </tr> </tbody> </table> <br /> ### Basic SELECT The {{%badge%}}SELECT{{%/badge%}} statement is used to select the columns from a base table and display its records. You should mention the names of the columns that are to be displayed in the result set. The syntax for using a basic {{%badge%}}SELECT{{%/badge%}} statement is: {{%code class="language-sql"%}}SELECT column_name(s) FROM base_table_name{{%/code%}} <br /> {{%bold%}}Example{{%/bold%}}: To select the _MovieID_ and _MovieName_ columns from the _Movies_ table, execute the following query: {{%code class="language-sql"%}}SELECT MovieID, MovieName from Movies{{%/code%}} <br /> It will generate the following output: <table class="content-table"> <thead> <tr> <th><strong>MovieID</strong></th> <th><strong>MovieName</strong></th> </tr> </thead> <tbody> <tr> <td>2056</td> <td>The First Purge</td> </tr> <tr> <td>2057</td> <td>Ant-Man and the Wasp</td> </tr> <tr> <td>2058</td> <td>Hotel Transylvania 3: Summer Vacation</td> </tr> <tr> <td>2059</td> <td>Skyscraper</td> </tr> </tbody> </table> <br /> For columns that have been named using integers, you need to add the backtick **`** punctuation to diffrencitate between column name and value. For example, a {{%badge%}}SELECT{{%/badge%}} query on a table called *Numbers* with a column named *01* can be written in the following manner {{%code class="language-sql"%}}SELECT `01` FROM Numbers{{%/code%}}<br /> {{%note%}}{{%bold%}}Note:{{%/bold%}} You can fetch a maximum of 20 columns and a maximum of 300 rows in one {{%badge%}}SELECT{{%/badge%}} query. If you require more records to be fetched, you can use the {{%link href="/en/cloud-scale/help/zcql/limit/" %}}LIMIT clause{{%/link%}} to iterate the query and specify the offset and value accordingly.{{%/note%}} <br /> ### SELECT \* You can fetch records from all the columns of a base table, instead of selecting records from a particular column or columns, by using a '\*' following the {{%badge%}}SELECT{{%/badge%}} statement. The '\*' denotes all the columns of the base table. The syntax for selecting all the columns from a base table is: {{%code class="language-sql"%}}SELECT * FROM base_table_name{{%/code%}} <br /> {{%note%}}{{%bold%}}Note:{{%/bold%}} {{%badge%}}SELECT \*{{%/badge%}} allows you to fetch a maximum of 300 rows in one query. If you require more records to be fetched, you can use the {{%link href="/en/cloud-scale/help/zcql/limit/" %}}LIMIT clause{{%/link%}} to iterate the query and specify the offset and value accordingly.{{%/note%}} {{%bold%}}Example{{%/bold%}}: To display the records from all the columns of the _Movies_ table, execute the following query: {{%code class="language-sql"%}}SELECT * FROM Movies{{%/code%}} This will display records from all the columns in the _Movies_ table. <table class="content-table"> <thead> <tr> <th><strong>MovieID</strong></th> <th><strong>MovieName</strong></th> <th><strong>ShowDate</strong></th> <th><strong>ShowTime</strong></th> <th><strong>TheaterID</strong></th> </tr> </thead> <tbody> <tr> <td>2056</td> <td>The First Purge</td> <td>2018-07-13</td> <td>13:00:00</td> <td>047</td> </tr> <tr> <td>2057</td> <td>Ant-Man and the Wasp</td> <td>2018-07-13</td> <td>14:20:00</td> <td>052</td> </tr> <tr> <td>2058</td> <td>Hotel Transylvania 3: Summer Vacation</td> <td>2018-07-14</td> <td>17:00:00</td> <td>052</td> </tr> <tr> <td>2059</td> <td>Skyscraper</td> <td>2018-07-14</td> <td>21:30:00</td> <td>053</td> </tr> </tbody> </table> Before we discuss the various clauses and statements available for the {{%badge%}}SELECT{{%/badge%}} operation, let's discuss the {{%badge%}}INSERT{{%/badge%}}, {{%badge%}}UPDATE{{%/badge%}}, and {{%badge%}}DELETE{{%/badge%}} operations. -------------------------------------------------------------------------------- title: "General Syntax of INSERT" description: "ZCQL is Catalyst's own query language modelled after familiar query languages that you can use to query your app's database efficiently." last_updated: "2026-03-18T07:41:08.535Z" source: "https://docs.catalyst.zoho.com/en/cloud-scale/help/zcql/insert/" service: "Cloud Scale" -------------------------------------------------------------------------------- # INSERT ### General Syntax of INSERT You can insert a record in a table by passing the values for each column of that table for that record. The general syntax of a basic ZCQL data insertion operation is as follows: {{%code class="language-sql"%}}INSERT INTO {BASE_TABLE_NAME} VALUES (VALUE1, VALUE2,..){{%/code%}} You must pass the values in the same order as the columns are present in the table. {{%note%}}{{%bold%}}Note:{{%/bold%}}<br /> * Ensure that you provide the right value for a column that matches the column's data type. * For columns where the _IsUnique_ constraint is enabled, ensure that you don't enter values that are already present in other records. Similarly, for the columns where the _IsMandatory_ validator is enabled, ensure that you do not leave the data value blank. * For a column where the data type is a {{%bold%}}Foreign Key{{%/bold%}}, you must provide the ROWID of the parent table's record which it refers, as its value. {{%/note%}} {{%bold%}}Example:{{%/bold%}} To insert a record in the _Movies_ table, execute the following query: {{%code class="language-sql"%}}INSERT INTO Movies VALUES (2060,'Mission: Impossible – Fallout', '2018-7-27', '16:00:00', 053){{%/code%}} {{%note%}}{{%bold%}}Note:{{%/bold%}} If the values to be inserted are {{%bold%}}String values{{%/bold%}}, you must pass the values enclosed in single quotations as shown above. {{%/note%}} ### Partial Insert You can also pass values only for specific columns in a table by specifying the column names which the values need to be inserted for. The syntax for a partial insert is as follows: {{%code class="language-sql"%}}INSERT INTO {BASE_TABLE_NAME} (COLUMN_NAME1, COLUMN_NAME2,..) VALUES (VALUE1, VALUE2,..){{%/code%}} To insert a record and pass values for the _MovieID_ and _TheaterID_ columns alone in the _Movies_ table, execute the following query: {{%code class="language-sql"%}}INSERT INTO Movies (MovieID, TheaterID) VALUES (2061, 052){{%/code%}} <br /> -------------------------------------------------------------------------------- title: "General Syntax of UPDATE" description: "ZCQL is Catalyst's own query language modelled after familiar query languages that you can use to query your app's database efficiently." last_updated: "2026-03-18T07:41:08.535Z" source: "https://docs.catalyst.zoho.com/en/cloud-scale/help/zcql/update/" service: "Cloud Scale" -------------------------------------------------------------------------------- # UPDATE ### General Syntax of UPDATE The {{%badge%}}UPDATE{{%/badge%}} command enables you to update specific column values of existing records of a table in the Data Store. You can update the values of columns based on certain conditions. These conditions can be specified using the {{%link href="#WHERE" %}}{{%badge%}}WHERE{{%/badge%}} clause{{%/link%}}, which we will discuss later on in detail. The general syntax of a basic ZCQL data updation operation is as follows: {{%code class="language-sql"%}}UPDATE {BASE_TABLE_NAME} SET {COLUMN_NAME}={VALUE}[WHERE {WHERE_CONDITION}]{{%/code%}} {{%note%}}{{%bold%}}Note:{{%/bold%}}<br /> * Ensure that you provide the right value for a column that matches the column's data type. * For columns where the _IsUnique_ constraint is enabled, ensure that you don't enter values that are already present in other records. Similarly, for the columns where the _IsMandatory_ validator is enabled, ensure that you do not leave the data value blank. * For a column where the data type is a {{%bold%}}Foreign Key{{%/bold%}}, you must provide the ROWID of the parent table's record which it refers, as its value. {{%/note%}} {{%bold%}}Example:{{%/bold%}} To update the value of a specific movie name referred to by its _MovieID_ in the _Movies_ table, execute the following query: {{%code class="language-sql"%}}UPDATE Movies SET MovieName='The Equalizer 2'WHERE MovieID=2056{{%/code%}} <br /> {{%note%}}{{%bold%}}Note:{{%/bold%}} Similar to data insertion, if the values to be updated are String values, you must pass the values enclosed in single quotations.{{%/note%}} -------------------------------------------------------------------------------- title: "General Syntax of DELETE" description: "ZCQL is Catalyst's own query language modelled after familiar query languages that you can use to query your app's database efficiently." last_updated: "2026-03-18T07:41:08.535Z" source: "https://docs.catalyst.zoho.com/en/cloud-scale/help/zcql/delete/" service: "Cloud Scale" -------------------------------------------------------------------------------- # DELETE ### General Syntax of DELETE The {{%badge%}}DELETE{{%/badge%}} command enables you to delete one or more records from a table permanently. You can indicate the records to be deleted using the {{%link href="/en/cloud-scale/help/zcql/where/" %}}{{%badge%}}WHERE{{%/badge%}} clause{{%/link%}}, which is explained in the next section, and specify the conditions. The general syntax of a basic {{%badge%}}DELETE{{%/badge%}} statement is as follows: {{%code class="language-sql"%}}DELETE FROM {BASE_TABLE_NAME}[WHERE {WHERE_CONDITION}]{{%/code%}} {{%bold%}}Example:{{%/bold%}} To delete the record of a specific show date in a particular theater from the Movies table, you can execute this query: {{%code class="language-sql"%}}DELETE FROM Movies WHERE MovieID=2059 AND ShowDate='2018-07-14' AND TheaterID=053{{%/code%}} <br /> {{%note%}}{{%bold%}}Note:{{%/bold%}} If you specify a string value in the {{%badge%}}WHERE{{%/badge%}} condition, you must pass it enclosed in a single quotation.{{%/note%}} -------------------------------------------------------------------------------- title: "WHERE Clause" description: "ZCQL is Catalyst's own query language modelled after familiar query languages that you can use to query your app's database efficiently." last_updated: "2026-03-18T07:41:08.535Z" source: "https://docs.catalyst.zoho.com/en/cloud-scale/help/zcql/where/" service: "Cloud Scale" -------------------------------------------------------------------------------- # WHERE Clause ### Introduction The {{%badge%}}WHERE{{%/badge%}} clause is used to filter the data records on the basis of a specified condition or a set of conditions in the search queries. When the WHERE condition is used in a query, the data records are verified based on the specified conditions, and the commands are executed only on the records that satisfy the conditions. For example, the syntax for using the {{%badge%}}WHERE{{%/badge%}} condition in a {{%badge%}}SELECT{{%/badge%}} statement is: {{%code class="language-sql"%}}SELECT column_name(s) FROM base_table_name WHERE condition{{%/code%}} The {{%badge%}}WHERE{{%/badge%}} condition can be used with the {{%badge%}}UPDATE{{%/badge%}} and {{%badge%}}DELETE{{%/badge%}} statements as well. <br /> ### Operators Supported by the WHERE Clause You can use the following operators in the {{%badge%}}WHERE{{%/badge%}} conditions in ZCQL queries: <table class="content-table"> <thead> <tr> <th class="w30p"><strong>Operators</strong></th> <th class="w70p"><strong>Description</strong></th> </tr> </thead> <tbody> <tr> <td>=</td> <td>Equal to</td> </tr> <tr> <td>IS</td> <td>TRUE if the operand is the same as the value</td> </tr> <tr> <td>IS NULL</td> <td>TRUE if the operand is a null value</td> </tr> <tr> <td>IS NOT NULL</td> <td>TRUE if the operand is not a null value</td> </tr> <tr> <td>!=</td> <td>Not equal to</td> </tr> <tr> <td>LIKE</td> <td>TRUE if the operand matches a pattern</td> </tr> <tr> <td>NOT LIKE</td> <td>TRUE if the operand does not match a pattern</td> </tr> <tr> <td>BETWEEN</td> <td>TRUE if the operand value is between the start and end values</td> </tr> <tr> <td>IN</td> <td>TRUE if the operand is equal to a list of expressions</td> </tr> <tr> <td>NOT IN</td> <td>TRUE if the operand is not equal to a list of expressions</td> </tr> <tr> <td>&gt;</td> <td>Greater than</td> </tr> <tr> <td>&gt;=</td> <td>Greater than or equal to</td> </tr> <tr> <td>&lt;</td> <td>Lesser than</td> </tr> <tr> <td>&lt;=</td> <td>Lesser than or equal to</td> </tr> </tbody> </table> <br /> {{%bold%}}Example{{%/bold%}}: To view a list of the movies that are screened on days other than July 13, 2018 from the Movies table, you can specify the date condition in the query using the {{%badge%}}WHERE{{%/badge%}} clause in the following way: {{%code class="language-sql"%}}SELECT MovieName, ShowDate FROM Movies WHERE ShowDate IS '2018-07-14'{{%/code%}} This will generate the following output: <table class="content-table"> <thead> <tr> <th><strong>MovieName</strong></th> <th><strong>ShowDate</strong></th> </tr> </thead> <tbody> <tr> <td>Hotel Transylvania 3: Summer Vacation</td> <td>2018-07-14</td> </tr> <tr> <td>Skyscraper</td> <td>2018-07-14</td> </tr> </tbody> </table> <br /> ### Multiple WHERE Conditions You can specify a maximum of five WHERE conditions in a single query. When multiple {{%badge%}}WHERE{{%/badge%}} conditions are specified, you can use either an {{%badge%}}AND{{%/badge%}} or an {{%badge%}}OR{{%/badge%}} operator to link the conditions together. The functionalities of the {{%badge%}}AND{{%/badge%}} and {{%badge%}}OR{{%/badge%}} operators are: * {{%badge%}}{{%bold%}}AND{{%/bold%}}{{%/badge%}}: Produces only the data records that satisfy both of the conditions that are associated with the {{%badge%}}AND{{%/badge%}} operator. * {{%badge%}}{{%bold%}}OR{{%/bold%}}{{%/badge%}}: Produces the data records that satisfy either of the conditions that are associated with the {{%badge%}}OR{{%/badge%}} operator. For example, the syntax for using multiple {{%badge%}}WHERE{{%/badge%}} conditions in a {{%badge%}}SELECT{{%/badge%}} statement is: {{%code class="language-sql"%}}SELECT column_name(s) FROM base_table_name WHERE condition_1 AND|OR condition_2.. AND|OR condition_5{{%/code%}} {{%bold%}}Example{{%/bold%}}: To view a list of the show dates and show times from the _Movies_ table for either 'The First Purge' or 'Skyscraper' movies that are screened on July 14, execute the following query: {{%code class="language-sql"%}}SELECT MovieName, ShowDate, ShowTime FROM Movies WHERE MovieName='The First Purge' OR MovieName='Skyscraper' AND ShowDate='2018-07-14'{{%/code%}} There is only one record that matches the above conditions. The query will therefore generate the following result: <table class="content-table"> <thead> <tr> <th><strong>MovieName</strong></th> <th><strong>ShowDate</strong></th> <th><strong>ShowTime</strong></th> </tr> </thead> <tbody> <tr> <td>Skyscraper</td> <td>2018-07-14</td> <td>21:30:00</td> </tr> </tbody> </table> <br /> ### LIKE Statement The {{%badge%}}LIKE{{%/badge%}} condition enables you to indicate records which contain a specific criteria and select all records that match that condition. For example, you can indicate a specific alphabet that the records you require to be fetched must begin with, or a particular value that they must contain. For example, the syntax of the {{%badge%}}LIKE{{%/badge%}} condition in an {{%badge%}}UPDATE{{%/badge%}} statement is as follows: {{%code class="language-sql"%}}UPDATE base_table_name SET column_name(s)=value(s) WHERE condition LIKE *value* {{%/code%}} The '\*' can be used either before or after, or both before and after, the value that you specify. The '\*' is essentially a placeholder that indicates that any value can be replaced in its stead. For example, to indicate that all records in a table that begin with the alphabet 'A' must be fetched, you can enter the {{%badge%}}LIKE{{%/badge%}} value as 'A\*'. This specifies that after the letter A, any values can follow. {{%bold%}}Example:{{%/bold%}} To update the records where the movie names begin with the letter 'S' in the _Movies_ table, execute the following query: {{%code class="language-sql"%}}UPDATE Movies SET ShowDate='2018-07-17' WHERE MovieName like 'S*'{{%/code%}} This will update the following record and set the value: <table class="content-table"> <thead> <tr> <th><strong>MovieID</strong></th> <th><strong>MovieName</strong></th> <th><strong>ShowDate</strong></th> <th><strong>ShowTime</strong></th> <th><strong>TheaterID</strong></th> </tr> </thead> <tbody> <tr> <td>2059</td> <td>Skyscraper</td> <td>2018-07-17</td> <td>21:30:00</td> <td>053</td> </tr> </tbody> </table> <br /> Wildcard syntax and symbols such as {{%bold%}}\*{{%/bold%}} and {{%bold%}}?{{%/bold%}} are also permitted to be used in ZCQL statements defined using the {{%badge%}}LIKE{{%/badge%}} condition statement. <table class="content-table"> <thead> <tr> <th class="w30p">Wildcard Syntax</th> <th class="w70p">Definition</th> </tr> </thead> <tbody> <tr> <td>{{%badge%}}\*{{%/badge%}}</td> <td>Matches zero or more characters</td> </tr> <tr> <td>{{%badge%}}{{%bold%}}?{{%/bold%}}{{%/badge%}}</td> <td>Matches exactly one character</td> </tr> </tbody> </table> ### BETWEEN Statement The {{%badge%}}BETWEEN{{%/badge%}} condition enables you to filter records by indicating the starting and ending values in a particular column containing numerical values. For example, you can select all the records where the percentage values are between 70 and 80. For example, the syntax of a {{%badge%}}BETWEEN{{%/badge%}} condition in a {{%badge%}}DELETE{{%/badge%}} statement is as follows: {{%code class="language-sql"%}}DELETE FROM base_table_name WHERE condition BETWEEN Value1 AND Value2{{%/code%}} <br /> {{%note%}}{{%bold%}}Note:{{%/bold%}} You can use the {{%badge%}}BETWEEN{{%/badge%}} statement only for selecting 'Int' or 'Double' values in the Catalyst Data Store table. You will not be able to use it for any other data types.{{%/note%}} {{%bold%}}Example:{{%/bold%}} To delete the records where the value of the MovieID is between 2056 and 2059 in the _Movies_ table, execute the following query: {{%code class="language-sql"%}}DELETE FROM Movies WHERE MovieID BETWEEN 2056 AND 2059{{%/code%}} The following records from the table will be deleted: <table class="content-table"> <thead> <tr> <th><strong>MovieID</strong></th> <th><strong>MovieName</strong></th> <th><strong>ShowDate</strong></th> <th><strong>ShowTime</strong></th> <th><strong>TheaterID</strong></th> </tr> </thead> <tbody> <tr> <td>2057</td> <td>Ant-Man and the Wasp</td> <td>2018-07-13</td> <td>14:20:00</td> <td>052</td> </tr> <tr> <td>2058</td> <td>Hotel Transylvania 3: Summer Vacation</td> <td>2018-07-14</td> <td>17:00:00</td> <td>052</td> </tr> </tbody> </table> {{%note%}}{{%bold%}}Note:{{%/bold%}} To produce the exact opposite result, you can use the {{%badge%}}NOT BETWEEN{{%/badge%}} statement. The same syntax and conditions of a {{%badge%}}BETWEEN{{%/badge%}} statement applies to {{%badge%}}NOT BETWEEN{{%/badge%}} statements. {{%/note%}} <br /> ### Column to Column Comparison You can compare two columns of the same table or in different tables by **referring to the column names with their respective table names** in the LHS and RHS of the comparison. The result of the comparison will return the rows that contain the same value for column1 and column2 in the table. This comparison support in {{%badge%}}WHERE{{%/badge%}} clause is applicable to the {{%badge%}}SELECT{{%/badge%}}, {{%badge%}}UPDATE{{%/badge%}} and {{%badge%}}DELETE{{%/badge%}} queries. You can compare any two columns that are of the following data types : {{%badge%}}BOOLEAN{{%/badge%}}, {{%badge%}}DOUBLE{{%/badge%}}, {{%badge%}}DATE{{%/badge%}}, {{%badge%}}DATETIME{{%/badge%}}, {{%badge%}}ENCRYPTED{{%/badge%}}, {{%badge%}}VARCHAR{{%/badge%}}, {{%badge%}}TEXT{{%/badge%}}, {{%badge%}}INT{{%/badge%}} and {{%badge%}}BIGINT{{%/badge%}}. {{%note%}}{{%bold%}}Note:{{%/bold%}} You cannot compare columns of two different data types.{{%/note%}} <br /> **SELECT:** The general syntax for column comparison within the same table is as follows: {{%code class="language-sql"%}}SELECT * FROM tablename WHERE columnname1 = tablename.columnname2{{%/code%}} **Example**:<br /> {{%code class="language-sql"%}}Select MovieName FROM Movies WHERE Actor = Movies.Producer{{%/code%}} <table class="content-table" style="width:30%"> <thead> <tr> <th>MovieName</th> </tr> </thead> <tbody> <tr> <td>The White River Kid</td> </tr> <tr> <td>Duplex</td> </tr> <tr> <td>Oppenheimer</td> </tr> </tbody> </table> **UPDATE:** {{%code class="language-sql"%}}UPDATE Movies SET self_produced = "Yes" WHERE Actor = Movies.Producer{{%/code%}} **DELETE:** {{%code class="language-sql"%}}DELETE FROM Movies WHERE Actor = Movies.Producer{{%/code%}} <br /> #### Comparison of Columns in Different Tables You can also compare columns of two different tables as shown in the syntax below : {{%code class="language-sql"%}}SELECT tablename1.columnname1, tablename2.columnname1 FROM tablename2 LEFT JOIN tablename1 ON tablename2.columnname1 = tablename1.columnname2 WHERE tablename1.columnname1 = tablename2.columnname2{{%/code%}} **Example**: {{%code class="language-sql"%}}SELECT Movies.name, Theatres.city from Theatres left join Movies on Theatres.ROWID = Movies.theatreID where Movies.distributionCity = theatre.city{{%/code%}} <br /> <table class="content-table"> <thead> <tr> <th>Name</th> <th>CIty</th> </tr> </thead> <tbody> <tr> <td>Dark Knight Rises</td> <td>Los Angeles</td> </tr> <tr> <td>Titanic</td> <td>New York</td> </tr> </tbody> </table> <br /> Similarly, you can compare columns belonging to two different tables while executing the {{%badge%}}UPDATE{{%/badge%}} and {{%badge%}}DELETE{{%/badge%}} queries. ### Subqueries in WHERE Clause A subquery is a query that is written inside another query statement. Catalyst enables you to execute simple singular subqueries with {{%badge%}}WHERE{{%/badge%}} clause. You can write subqueries with the {{%badge%}}WHERE{{%/badge%}} clause in {{%badge%}}SELECT{{%/badge%}}, {{%badge%}}UPDATE{{%/badge%}} and {{%badge%}}DELETE{{%/badge%}} queries. The syntax of a subquery with {{%badge%}}WHERE{{%/badge%}} clause is given below: {{%code class="language-sql"%}}SELECT column_name FROM table_name WHERE column_name operator (SELECT column_name FROM table_name){{%/code%}} <br /> **Example Database** The employee details of Zylker Technologies are being maintained in the {{%link href="/en/cloud-scale/help/data-store/introduction/" %}}Data Store{{%/link%}} in the *Zylker_Employee_DB* table. The table contains the following columns and rows: <table class="content-table"> <thead> <tr> <th class="w15p">ID</th> <th class="w30p">Name</th> <th class="w30p">Department</th> <th class="w25p">Salary</th> </tr> </thead> <tbody> <tr> <td>ZT-001</td> <td>Amelia Burrows</td> <td>Product Management</td> <td>15000</td> </tr> <tr> <td>ZT-2001</td> <td>Bruce Wayne</td> <td>Sr. Management</td> <td>85000</td> </tr> <tr> <td>ZT-239</td> <td>Clark Kane</td> <td>Media Relations</td> <td>85000</td> </tr> <tr> <td>ZT-4289</td> <td>Michelle Mascarenhas</td> <td>Finance</td> <td>89000</td> </tr> </tbody> </table> An example subquery statement to list the salaries and the names of the employees who earn greater than the minimum salaried employee is given below: {{%code class="language-sql"%}}SELECT Name, Salary FROM Zylker_Employee_DB WHERE Salary > (SELECT MIN (Salary) FROM Zylker_Employee_DB){{%/code%}} <br /> <br /> <table class="content-table"> <thead> <tr> <th class="w50p">Zylker_Employee_DB.Salary</th> <th class="w50p">Zylker_Employee_DB.Name</th> </tr> </thead> <tbody> <tr> <td>15000</td> <td>Amelia Burrows</td> </tr> <tr> <td>85000</td> <td>Bruce Wayne</td> </tr> <tr> <td>89000</td> <td>Michelle Mascarenhas</td> </tr> </tbody> </table> {{%note%}}{{%bold%}}Note:{{%/bold%}} * This functionality is only available in {{%bold%}}ZCQL V2{{%/bold%}}. * From {{%bold%}}December 01st, 2024{{%/bold%}}, all your current projects in all your {{%link href="/en/getting-started/catalyst-organizations/" %}}Orgs{{%/link%}} present in the {{%link href="/en/deployment-and-billing/environments/development-environment/" %}}Development Environment{{%/link%}} will be automatically mapped to {{%bold%}}ZCQL V2 Parser{{%/bold%}}. * From {{%bold%}}April 01st, 2025{{%/bold%}}, all the projects present in all {{%italics%}}Orgs{{%/italics%}} that have already been mapped to {{%bold%}}ZCQL V2 Parser{{%/bold%}} in {{%italics%}}Development Environment{{%/italics%}} will be automatically mapped to {{%bold%}}ZCQL V2 Parser{{%/bold%}} in the {{%link href="/en/deployment-and-billing/environments/production-environment/" %}}Production Environment{{%/link%}}, if and when production is enabled for the project.{{%/note%}} <br /> -------------------------------------------------------------------------------- title: "HAVING Clause" description: "ZCQL is Catalyst's own query language modelled after familiar query languages that you can use to query your app's database efficiently." last_updated: "2026-03-18T07:41:08.536Z" source: "https://docs.catalyst.zoho.com/en/cloud-scale/help/zcql/having/" service: "Cloud Scale" -------------------------------------------------------------------------------- # HAVING Clause ### Introduction The {{%badge%}}HAVING{{%/badge%}} clause is used to select data records based on a specified condition. You can also apply {{%link href="/en/cloud-scale/help/zcql/zcql-functions/" %}}ZCQL functions{{%/link%}} in the query using this clause. The {{%badge%}}HAVING{{%/badge%}} clause can only be used with {{%link href="/en/cloud-scale/help/zcql/select/" %}}{{%badge%}}SELECT{{%/badge%}}{{%/link%}} queries. The syntax for using the {{%badge%}}HAVING{{%/badge%}} clause is shown below: {{%code class="language-sql"%}}SELECT column_name FROM base_table_name GROUP BY column_name HAVING column_name OPERATOR condition{{%/code%}} ### Operators Supported by the HAVING Clause You can use the following operators in the {{%badge%}}HAVING{{%/badge%}} conditions in ZCQL {{%badge%}}SELECT{{%/badge%}} queries: <table class="content-table"> <thead> <tr> <th class="w25p">Operators</th> <th class="w75p">Description</th> </tr> </thead> <tbody> <tr> <td>=</td> <td>Equal to</td> </tr> <tr> <td>IS</td> <td>TRUE if the operand is the same as the value</td> </tr> <tr> <td>IS NULL</td> <td>TRUE if the operand is a null value</td> </tr> <tr> <td>IS NOT NULL</td> <td>TRUE if the operand is not a null value</td> </tr> <tr> <td>=!</td> <td>Not equal to</td> </tr> <tr> <td>LIKE</td> <td>TRUE if the operand matches a pattern</td> </tr> <tr> <td>NOT LIKE</td> <td>TRUE if the operand does not match a pattern</td> </tr> <tr> <td>BETWEEN</td> <td>TRUE if the operand value is between the start and end values</td> </tr> <tr> <td>IN</td> <td>TRUE if the operand is equal to a list of expressions</td> </tr> <tr> <td>NOT IN</td> <td>TRUE if the operand is not equal to a list of expressions</td> </tr> <tr> <td>&gt;</td> <td>Greater than</td> </tr> <tr> <td>&gt;=</td> <td>Greater than or equal to</td> </tr> <tr> <td>&lt;</td> <td>Lesser than</td> </tr> <tr> <td>&lt;=</td> <td>Lesser than or equal to</td> </tr> </tbody> </table> ### ZCQL Functions with HAVING Clause {{%link href="/en/cloud-scale/help/zcql/zcql-functions/" %}}ZCQL functions{{%/link%}} like {{%badge%}}SUM(){{%/badge%}}, {{%badge%}}COUNT(){{%/badge%}}, {{%badge%}}AVG(){{%/badge%}}, etc., can be used with the {{%badge%}}HAVING{{%/badge%}} clause in a {{%badge%}}SELECT{{%/badge%}} query. **Example Database**: The employee details of Zylker Technologies are being maintained in the {{%link href="/en/cloud-scale/help/data-store/introduction/" %}}Data Store{{%/link%}} in the *Zylker_Employee_DB* table. The table contains the following columns and rows: <table class="content-table"> <thead> <tr> <th class="w15p">ID</th> <th class="w30p">Name</th> <th class="w30p">Department</th> <th class="w25p">Salary</th> </tr> </thead> <tbody> <tr> <td>ZT-001</td> <td>Amelia Burrows</td> <td>Product Management</td> <td>15000</td> </tr> <tr> <td>ZT-2001</td> <td>Bruce Wayne</td> <td>Sr. Management</td> <td>85000</td> </tr> <tr> <td>ZT-239</td> <td>Clark Kane</td> <td>Media Relations</td> <td>85000</td> </tr> <tr> <td>ZT-4289</td> <td>Michelle Mascarenhas</td> <td>Finance</td> <td>89000</td> </tr> </tbody> </table> Lets, use the {{%link href="/en/cloud-scale/help/zcql/zcql-functions/#avg" %}}{{%badge%}}AVG(){{%/badge%}}{{%/link%}} ZCQL function, with the {{%badge%}}HAVING{{%/badge%}} clause in an example {{%badge%}}SELECT{{%/badge%}} query. <br /> {{%code class="language-sql"%}}SELECT Name, Department, Salary FROM Zylker_Employee_DB GROUP BY Department HAVING AVG(Salary) > 50000{{%/code%}} <br /> <table class="content-table"> <thead> <tr> <th class="w30p">Zylker_Employee_DB.Salary</th> <th class="w35p">Zylker_Employee_DB.Department</th> <th class="w35p">Zylker_Employee_DB.Name</th> </tr> </thead> <tbody> <tr> <td>85000</td> <td>Sr. Management</td> <td>Bruce Wayne</td> </tr> <tr> <td>89000</td> <td>Finance</td> <td>Michelle Mascarenhas</td> </tr> </tbody> </table> {{%note%}}{{%bold%}}Note:{{%/bold%}} * This functionality is only available in {{%bold%}}ZCQL V2{{%/bold%}}. * From {{%bold%}}December 01st, 2024{{%/bold%}}, all your current projects in all your {{%link href="/en/getting-started/catalyst-organizations/" %}}Orgs{{%/link%}} present in the {{%link href="/en/deployment-and-billing/environments/development-environment/" %}}Development Environment{{%/link%}} will be automatically mapped to {{%bold%}}ZCQL V2 Parser{{%/bold%}}. * From {{%bold%}}April 01st, 2025{{%/bold%}}, all the projects present in all {{%italics%}}Orgs{{%/italics%}} that have already been mapped to {{%bold%}}ZCQL V2 Parser{{%/bold%}} in {{%italics%}}Development Environment{{%/italics%}} will be automatically mapped to {{%bold%}}ZCQL V2 Parser{{%/bold%}} in the {{%link href="/en/deployment-and-billing/environments/production-environment/" %}}Production Environment{{%/link%}}, if and when production is enabled for the project.{{%/note%}} -------------------------------------------------------------------------------- title: "JOIN Clause" description: "ZCQL is Catalyst's own query language modelled after familiar query languages that you can use to query your app's database efficiently." last_updated: "2026-03-18T07:41:08.536Z" source: "https://docs.catalyst.zoho.com/en/cloud-scale/help/zcql/joins/" service: "Cloud Scale" -------------------------------------------------------------------------------- # JOIN Clause ### Introduction The {{%badge%}}JOIN{{%/badge%}} clause is used to combine rows from two or more tables in a {{%badge%}}SELECT{{%/badge%}} query, based on a related column between them. The rows from all the tables are generated together with the common column merged. Before we learn more about the {{%badge%}}JOIN{{%/badge%}} clause, let's understand a few key terms related to joins: 1. {{%bold%}}Primary Key:{{%/bold%}} A primary key is a unique identifier of a record in a table. A table can have one column as its primary key column, and the value for each record in this column must be unique, is mandatory, and cannot be duplicated. 2. {{%bold%}}Foreign Key{{%/bold%}}: A foreign key column in one table uniquely identifies a row in another table or the same table. The foreign key of the second table refers to the primary key of the first table. 3. {{%bold%}}Parent Table{{%/bold%}}: In the {{%badge%}}JOIN{{%/badge%}} clause, the parent table is the base table that the join is performed based on. 4. {{%bold%}}Join Table{{%/bold%}}: The join table is the secondary table or the child table which is being merged with the parent table in the {{%badge%}}JOIN{{%/badge%}} clause. {{%note%}}{{%bold%}}Note:{{%/bold%}} Joins between two tables are only possible if there is a relationship between them. One of the tables must refer to the other table's primary key column, so either the join table must have a foreign key column of the parent table, or the parent table must have a foreign key column of the join table.{{%/note%}} In our example, the _MovieID_ column is the primary key of the _Movies_ table as it holds the unique identification number of a movie. <br /> {{%bold%}}Example Database:{{%/bold%}} Let's create a table named "Theaters" in the ticket booking application which specifies the locations of all the theaters that are registered with the application. Sample records from the {{%bold%}}Theaters{{%/bold%}} table are given below: <table class="content-table"> <thead> <tr> <th><strong>TheaterID</strong></th> <th><strong>TheaterName</strong></th> <th><strong>Location</strong></th> </tr> </thead> <tbody> <tr> <td>047</td> <td>The Express Cinemas</td> <td>New York City</td> </tr> <tr> <td>048</td> <td>ANC Cinemas</td> <td>Rochester</td> </tr> <tr> <td>052</td> <td>Cosmos Theater</td> <td>Albany</td> </tr> <tr> <td>053</td> <td>FunTime Cinemas</td> <td>Buffalo</td> </tr> </tbody> </table> The _TheaterID_ column is the primary key of the _Theaters_ table as it holds the unique identification numbers of the theaters. The _TheaterID_ column in the _Movies_ table is the foreign key to the _TheaterID_ column in the _Theaters_ table. {{%note%}}{{%bold%}}Note:{{%/bold%}} When you use a {{%badge%}}JOIN{{%/badge%}} clause, you must specify the column names and their table names for each column in the query as: {{%bold%}}'_table\_name.column\_name_'{{%/bold%}}'. For example: '_Movies.MovieName_', '_Theaters.Location_'{{%/note%}} If you don't specify the table name with the column name in a query, then by default it is considered to be a column in the base table. <br /> ### Types of Joins There are two types of Joins that can be performed in ZCQL. <br /> <br /> #### INNER JOIN The {{%badge%}}INNER JOIN{{%/badge%}} returns the records that have matching values in both the parent table and the join table. When you use an inner join on two tables, only the records whose values match for the specified columns in both the tables are produced as the output.<br /> The syntax for using an {{%badge%}}INNER JOIN{{%/badge%}} is:<br /> {{%code class="language-sql"%}}SELECT column_name(s) FROM parent_table_name INNER JOIN join_table_name ON parent_table_name.column_name = join_table_name.column_name{{%/code%}} {{%bold%}}Example:{{%/bold%}}<br /> To view a list of the movie names, show dates, and show times from the _Movies_ table along with the theater names from the _Theaters_ table, execute the following query: <br /> {{%code class="language-sql"%}}SELECT Movies.MovieName, Theatres.TheaterName, Movies.ShowDate, Movies.ShowTime, FROM Movies INNER JOIN Theatres ON Movies.TheaterID = Theatres.TheaterID{{%/code%}} This will generate the following output: <table class="content-table"> <thead> <tr> <th><strong>MovieName</strong></th> <th><strong>TheaterName</strong></th> <th><strong>ShowDate</strong></th> <th><strong>ShowTime</strong></th> </tr> </thead> <tbody> <tr> <td>Ant-Man and the Wasp</td> <td>The Express Cinemas</td> <td>2018-07-13</td> <td>13:30:00</td> </tr> <tr> <td>Hotel Transylvania 3: Summer Vacation</td> <td>Cosmos Theater</td> <td>2018-07-13</td> <td>14:20:00</td> </tr> <tr> <td>The First Purge</td> <td>Cosmos Theater</td> <td>2018-07-14</td> <td>17:00:00</td> </tr> <tr> <td>The First Purge</td> <td>FunTime Cinemas</td> <td>2018-07-14</td> <td>21:30:00</td> </tr> </tbody> </table> <br /> #### LEFT JOIN The {{%badge%}}LEFT JOIN{{%/badge%}} returns all the records from the parent table and the matched records from the join table. When you use a left join on two tables, all records from the parent table are returned, even if they do not have matches from the join table. The result is displayed as 'NULL' for records that do not have a match in the join table. <br /> The syntax for using a {{%badge%}}LEFT JOIN{{%/badge%}} is:<br /> {{%code class="language-sql"%}}SELECT column_name(s) FROM parent_table_name LEFT JOIN join_table_name ON parent_table_name.column_name = join_table_name.column_name{{%/code%}} {{%bold%}}Example:{{%/bold%}}<br /> To view a list of theaters and their locations from the _Theaters_ table along with the movies that are being screened in the theaters from the _Movies_ table, execute the following query:<br /> {{%code class="language-sql"%}}SELECT Theaters.TheaterName, Theaters.Location, Movies.MovieName FROM Theaters LEFT JOIN Movies ON Theaters.TheaterID = Movies.TheaterID{{%/code%}} This will generate the following output:<br /> <table class="content-table"> <thead> <tr> <th><strong>TheaterName</strong></th> <th><strong>Location</strong></th> <th><strong>MovieName</strong></th> </tr> </thead> <tbody> <tr> <td>The Express Cinemas</td> <td>New York City</td> <td>The First Purge</td> </tr> <tr> <td>ANC Cinemas</td> <td>Rochester</td> <td><strong>NULL</strong></td> </tr> <tr> <td>Cosmos Theater</td> <td>Albany</td> <td>Hotel Transylvania 3: Summer Vacation</td> </tr> <tr> <td>FunTime Cinemas</td> <td>Buffalo</td> <td>Skyscraper</td> </tr> </tbody> </table> Since there are no matching records for ANC Cinemas in the Movies table, the result is displayed as 'NULL'. <br /> <br /> ### Multiple Joins You can combine a maximum of **four** joins in a single ZCQL query. However, you can only write **one** {{%badge%}}JOIN{{%/badge%}} condition for each join clause. This will display the results from four different tables, where one is linked to another using an {{%badge%}}INNER JOIN{{%/badge%}} or a {{%badge%}}LEFT JOIN{{%/badge%}}. {{%bold%}}Example Database{{%/bold%}}: Let's create a table called 'Pricing' in the ticket booking application which displays the ticket price in dollars of a single ticket for a particular movie and theater. This table references _TheaterID_ from the _Theaters_ table and _MovieID_ from the _Movies_ table as the foreign keys. Sample records from the {{%bold%}}Pricing{{%/bold%}} table are given below: <table class="content-table"> <thead> <tr> <th><strong>TheaterID</strong></th> <th><strong>MovieID</strong></th> <th><strong>Price</strong></th> </tr> </thead> <tbody> <tr> <td>047</td> <td>2056</td> <td>9.20</td> </tr> <tr> <td>048</td> <td>NULL</td> <td><strong>NULL</strong></td> </tr> <tr> <td>052</td> <td>2057</td> <td>8.64</td> </tr> <tr> <td>052</td> <td>2058</td> <td>11.50</td> </tr> <tr> <td>053</td> <td>2059</td> <td>7.44</td> </tr> </tbody> </table> You can execute a query to display the _TheaterName_ from the _Theaters_ table, _MovieName_, _ShowDate_, and _ShowTime_ from the _Movies_ table, and _Price_ from the _Pricing_ table, by executing the following query: {{%code class="language-sql"%}}SELECT Theaters.TheaterName,Movies.MovieName, Movies.ShowDate, Movies.ShowTime, Pricing.Price FROM Pricing LEFT JOIN Movies ON Theaters.TheaterID = Movies.TheaterID INNER JOIN Theaters ON Movies.MovieID = Pricing.MovieID{{%/code%}} This will generate the following output: <table class="content-table"> <thead> <tr> <th><strong>TheaterName</strong></th> <th><strong>MovieName</strong></th> <th><strong>ShowDate</strong></th> <th><strong>ShowTime</strong></th> <th><strong>Price</strong></th> </tr> </thead> <tbody> <tr> <td>The Express Cinemas</td> <td>The First Purge</td> <td>2018-07-13</td> <td>13:00:00</td> <td>9.20</td> </tr> <tr> <td>ANC Cinemas</td> <td>NULL</td> <td>NULL</td> <td>NULL</td> <td>NULL</td> </tr> <tr> <td>Cosmos Theater</td> <td>Ant-Man and the Wasp</td> <td>2018-07-13</td> <td>14:20:00</td> <td>8.64</td> </tr> <tr> <td>Cosmos Theater</td> <td>Hotel Transylvania 3: Summer Vacation</td> <td>2018-07-14</td> <td>17:00:00</td> <td>11.50</td> </tr> <tr> <td>FunTime Cinemas</td> <td>Skyscraper</td> <td>2018-07-14</td> <td>21:30:00</td> <td>7.44</td> </tr> </tbody> </table> {{%note%}}{{%bold%}}Note:{{%/bold%}} * To utilize all the features of the {{%badge%}}JOIN{{%/badge%}} clause, we recommend you migrate your codebase from {{%bold%}}ZCQL V1{{%/bold%}} to {{%bold%}}ZCQL V2{{%/bold%}}. * From {{%bold%}}December 01st, 2024{{%/bold%}}, all your current projects in all your {{%link href="/en/getting-started/catalyst-organizations/" %}}Orgs{{%/link%}} present in the {{%link href="/en/deployment-and-billing/environments/development-environment/" %}}Development Environment{{%/link%}} will be automatically mapped to {{%bold%}}ZCQL V2 Parser{{%/bold%}}. * From {{%bold%}}April 01st, 2025{{%/bold%}}, all the projects present in all {{%italics%}}Orgs{{%/italics%}} that have already been mapped to {{%bold%}}ZCQL V2 Parser{{%/bold%}} in {{%italics%}}Development Environment{{%/italics%}} will be automatically mapped to {{%bold%}}ZCQL V2 Parser{{%/bold%}} in the {{%link href="/en/deployment-and-billing/environments/production-environment/" %}}Production Environment{{%/link%}}, if and when production is enabled for the project. * You can learn more on the enhancements and features of {{%bold%}}ZCQL V2{{%/bold%}} from this {{%link href="/en/cloud-scale/help/zcql/syntax-exceptions/" %}}help document{{%/link%}}.{{%/note%}} -------------------------------------------------------------------------------- title: "GROUP BY and ORDER BY" description: "ZCQL is Catalyst's own query language modelled after familiar query languages that you can use to query your app's database efficiently" last_updated: "2026-03-18T07:41:08.536Z" source: "https://docs.catalyst.zoho.com/en/cloud-scale/help/zcql/groupby-orderby/" service: "Cloud Scale" -------------------------------------------------------------------------------- ### GROUP BY Statement The {{%badge%}}GROUP BY{{%/badge%}} statement is used to group the records fetched in a search query results by one or more columns. {{%badge%}}GROUP BY{{%/badge%}} enables identical data to be grouped together and displayed sequentially. When there are duplicate values in a column, the {{%badge%}}GROUP BY{{%/badge%}} statement displays the duplicates together. The records are then ordered based on other columns. The {{%badge%}}GROUP BY{{%/badge%}} statement is associated with the {{%badge%}}SELECT{{%/badge%}} statement and is often used with {{%link href="/en/cloud-scale/help/zcql/zcql-functions/" %}}ZCQL functions{{%/link%}}. It is used towards the end of the query and should therefore satisfy the {{%badge%}}JOIN{{%/badge%}} statements and follow the {{%badge%}}WHERE{{%/badge%}} conditions. The basic syntax for using a {{%badge%}}GROUP BY{{%/badge%}} statement along with the {{%badge%}}SELECT{{%/badge%}} statement is as follows: {{%code class="language-sql"%}}SELECT column_name(s) FROM parent_table_name GROUP BY column_name(s) {{%/code%}} <br /> {{%bold%}}Example{{%/bold%}}: To view a list of theaters and their locations from the _Theaters_ table, grouped by the location of the theaters, execute the following query: {{%code class="language-sql"%}}SELECT TheaterName, Location FROM Theaters GROUP BY Location {{%/code%}} <br /> This will generate the following output: <table class="content-table"> <thead> <tr> <th><strong>TheaterName</strong></th> <th><strong>Location</strong></th> </tr> </thead> <tbody> <tr> <td>Cosmos Theater</td> <td>Albany</td> </tr> <tr> <td>FunTime Cinemas</td> <td>Buffalo</td> </tr> <tr> <td>The Express Cinemas</td> <td>New York City</td> </tr> <tr> <td>ANC Cinemas</td> <td>Rochester</td> </tr> </tbody> </table> #### BINARYOF() Function The {{%badge%}}BINARYOF(){{%/badge%}} function can only be used in a {{%badge%}}GROUP BY{{%/badge%}} statement. The function can be used in places where you wish to produce a output that is not case sensitive and considers all the values in a coulumn of a table. The {{%badge%}}BINARYOF(){{%/badge%}} function can only be used on columns with **VarChar** or **Text** datatype. For example, consider the following table *Zylker_EMP*: <table class="content-table" style="width:30%"> <thead> <tr> <th>Names</th> </tr> </thead> <tbody> <tr> <td>AMELIA BURROWS</td> </tr> <tr> <td>amelia burrows</td> </tr> <tr> <td>amelia Burrows</td> </tr> <tr> <td>Amelia Burrows</td> </tr> </tbody> </table> An example ZCQL statement to illustrate the functionality of {{%badge%}}BINARYOF(){{%/badge%}} and its possible output is given below: {{%code class="language-sql"%}}SELECT Names FROM Zylker_EMP GROUP BY BINARYOF(Names){{%/code%}}<br /> <table class="content-table" style="width:30%"> <thead> <tr> <th>Zylker_EMP.Names</th> </tr> </thead> <tbody> <tr> <td>AMELIA BURROWS</td> </tr> <tr> <td>amelia burrows</td> </tr> <tr> <td>amelia Burrows</td> </tr> <tr> <td>Amelia Burrows</td> </tr> </tbody> </table> {{%note%}}{{%bold%}}Note:{{%/bold%}} * This functionality is only available in {{%bold%}}ZCQL V2{{%/bold%}}. * From {{%bold%}}December 01st, 2024{{%/bold%}}, all your current projects in all your {{%link href="/en/getting-started/catalyst-organizations/" %}}Orgs{{%/link%}} present in the {{%link href="/en/deployment-and-billing/environments/development-environment/" %}}Development Environment{{%/link%}} will be automatically mapped to {{%bold%}}ZCQL V2 Parser{{%/bold%}}. * From {{%bold%}}April 01st, 2025{{%/bold%}}, all the projects present in all {{%italics%}}Orgs{{%/italics%}} that have already been mapped to {{%bold%}}ZCQL V2 Parser{{%/bold%}} in {{%italics%}}Development Environment{{%/italics%}} will be automatically mapped to {{%bold%}}ZCQL V2 Parser{{%/bold%}} in the {{%link href="/en/deployment-and-billing/environments/production-environment/" %}}Production Environment{{%/link%}}, if and when production is enabled for the project.{{%/note%}} ### ORDER BY Statement The {{%badge%}}ORDER BY{{%/badge%}} statement is used to sort the records fetched in a search query results in an ascending or a descending order, based on one or more columns. When the {{%badge%}}ORDER BY{{%/badge%}} statement is used to sort textual data, it sorts the records in the alphabetical order. Similar to the {{%badge%}}GROUP BY{{%/badge%}} statement, the {{%badge%}}ORDER BY{{%/badge%}} statement is associated with the {{%badge%}}SELECT{{%/badge%}} statement and is often used after the {{%badge%}}GROUP BY{{%/badge%}} statement. It is used towards the end of the query, after the {{%badge%}}JOIN{{%/badge%}} clause statements or the WHERE conditions, if present, but before the {{%badge%}}LIMIT{{%/badge%}} clause. The {{%badge%}}ORDER BY{{%/badge%}} statement should then satisfy the {{%badge%}}JOIN{{%/badge%}} statements and follow the {{%badge%}}WHERE{{%/badge%}} conditions. {{%note%}}{{%bold%}}Note{{%/bold%}}: If the {{%badge%}}ORDER BY{{%/badge%}} statement follows a GROUP BY statement, the {{%badge%}}ORDER BY{{%/badge%}} statement takes precedence and the results are ordered first based on the {{%badge%}}ORDER BY{{%/badge%}} statement, and then grouped together based on the {{%badge%}}GROUP BY{{%/badge%}} statement.{{%/note%}} * If there are duplicate values in a column, the {{%badge%}}ORDER BY{{%/badge%}} statement will display the duplicates together. The records are then ordered based on other columns. * {{%link href="/en/cloud-scale/help/zcql/zcql-functions/" %}}ZCQL Functions{{%/link%}} can also be used with {{%badge%}}ORDER BY{{%/badge%}} statements. {{%note%}}{{%bold%}}Note:{{%/bold%}} To utilize all the features of the {{%badge%}}ORDER BY{{%/badge%}} statement, we recommend you migrate your codebase from {{%bold%}}ZCQL V1{{%/bold%}} to {{%bold%}}ZCQL V2{{%/bold%}}. You can learn more on the enhancements and features of {{%bold%}}ZCQL V2{{%/bold%}} from this {{%link href="/en/cloud-scale/help/zcql/syntax-exceptions/" %}}help document{{%/link%}}{{%/note%}} The basic syntax for using a {{%badge%}}ORDER BY{{%/badge%}} statement with the {{%badge%}}SELECT{{%/badge%}} statement is as follows: {{%code class="language-sql"%}}SELECT column_name(s) FROM parent_table_name ORDER BY column_name(s) [ASC | DESC]{{%/code%}} {{%note%}}{{%bold%}}Note:{{%/bold%}}<br /> * By default, the {{%badge%}}ORDER BY{{%/badge%}} statement sorts the records in ascending order without using the keyword '{{%badge%}}ASC{{%/badge%}}'. To sort the records in a descending order, you will have to use the keyword '{{%badge%}}DESC{{%/badge%}}'. * In all columns where you use the {{%badge%}}ORDER BY{{%/badge%}} clause, the {{%badge%}}ASC{{%/badge%}} and {{%badge%}}DESC{{%/badge%}} functionality can now be applied. You can also apply {{%badge%}}ASC{{%/badge%}} and {{%badge%}}DESC{{%/badge%}} for individual columns.{{%/note%}} <br /> {{%bold%}}Example{{%/bold%}}: To view a list of the ticket prices from the Pricing table in descending order for each movie from the _Movies_ table execute the following query: {{%code class="language-sql"%}}SELECT Movies.MovieName, Pricing.Price FROM Movies INNER JOIN Pricing ON Movies.MovieID = Pricing.MovieID ORDER BY Pricing.Price DESC {{%/code%}} <br /> This will generate the following output: <table class="content-table"> <thead> <tr> <th><strong>MovieName</strong></th> <th><strong>Price</strong></th> </tr> </thead> <tbody> <tr> <td>Hotel Transylvania 3: Summer Vacation</td> <td>11.50</td> </tr> <tr> <td>The First Purge</td> <td>9.20</td> </tr> <tr> <td>Ant-Man and the Wasp</td> <td>8.64</td> </tr> <tr> <td>Skyscraper</td> <td>7.44</td> </tr> </tbody> </table> -------------------------------------------------------------------------------- title: "LIMIT Clause" description: "ZCQL is Catalyst's own query language modelled after familiar query languages that you can use to query your app's database efficiently." last_updated: "2026-03-18T07:41:08.536Z" source: "https://docs.catalyst.zoho.com/en/cloud-scale/help/zcql/limit/" service: "Cloud Scale" -------------------------------------------------------------------------------- ### LIMIT Clause The {{%badge%}}LIMIT{{%/badge%}} clause limits the number of data records that are displayed in the result set of a search query. It contains the following properties: 1. {{%bold%}}{{%badge%}}OFFSET{{%/badge%}}{{%/bold%}}: OFFSET defines the starting index of the result, i.e., the first record's position that will be displayed in the result. 2. {{%bold%}}{{%badge%}}VALUE{{%/badge%}}{{%/bold%}}: VALUE defines the number of records to be retrieved from the starting index, i.e., the {{%badge%}}OFFSET{{%/badge%}}. The {{%badge%}}LIMIT{{%/badge%}} clause identifies the {{%badge%}}OFFSET{{%/badge%}} index and then displays the number of records as specified in the {{%badge%}}VALUE{{%/badge%}} from the {{%badge%}}OFFSET{{%/badge%}} index. You can also just specify the value without specifying the offset, when the starting index is the first record. The {{%badge%}}LIMIT{{%/badge%}} clause is only used when a specific number of records is to be displayed. It is used towards the end of the query after all the other clauses and statements. The basic syntax for using the {{%badge%}}LIMIT{{%/badge%}} clause with a {{%badge%}}SELECT{{%/badge%}} statement is: {{%code class="language-sql"%}}SELECT column_name(s) FROM parent_table_name LIMIT OFFSET, VALUE{{%/code%}} <br /> {{%bold%}}Example{{%/bold%}}: To view a list of the movies from the _Movies_ table consisting of up to three records from the second starting index execute the following query: {{%code class="language-sql"%}}SELECT * FROM Movies LIMIT 1,3 {{%/code%}} It will generate the following output: <table class="content-table"> <thead> <tr> <th><strong>MovieID</strong></th> <th><strong>MovieName</strong></th> <th><strong>ShowDate</strong></th> <th><strong>ShowTime</strong></th> <th><strong>TheaterID</strong></th> </tr> </thead> <tbody> <tr> <td>2057</td> <td>Ant-Man and the Wasp</td> <td>2018-07-13</td> <td>14:20:00</td> <td>052</td> </tr> <tr> <td>2058</td> <td>Hotel Transylvania 3: Summer Vacation</td> <td>2018-07-14</td> <td>17:00:00</td> <td>052</td> </tr> <tr> <td>2059</td> <td>Skyscraper</td> <td>2018-07-14</td> <td>21:30:00</td> <td>053</td> </tr> </tbody> </table> -------------------------------------------------------------------------------- title: "ZCQL Functions" description: "ZCQL is Catalyst's own query language modelled after familiar query languages that you can use to query your app's database efficiently." last_updated: "2026-03-18T07:41:08.537Z" source: "https://docs.catalyst.zoho.com/en/cloud-scale/help/zcql/zcql-functions/" service: "Cloud Scale" -------------------------------------------------------------------------------- # ZCQL Functions ZCQL supports some mathematical and analytical built-in functions that help you perform quick arithmetic operations on the data and manipulate the result set of a search query to meet your requirements. These functions are used in the {{%badge%}}SELECT{{%/badge%}} statement for the columns that are generated in the output. ### MIN() <br /> The {{%badge%}}MIN(){{%/badge%}} function returns the minimum value of the selected column. You can use it to return the smallest value of a column that contains numerical data or the record that comes first in the alphabetical order.<br /> The basic syntax for using a {{%badge%}}MIN(){{%/badge%}} function along with the {{%badge%}}SELECT{{%/badge%}} statement is:<br /> {{%code class="language-sql"%}}SELECT MIN(column_name) FROM base_table_name {{%/code%}} {{%bold%}}Example{{%/bold%}}: To view the least expensive movie from the _Pricing_ table, execute the following query:<br /> {{%code class="language-sql"%}}SELECT MIN(Price) FROM Pricing {{%/code%}} This will generate the following output: <table class="content-table"> <thead> <tr> <th><strong>MIN(Price)</strong></th> </tr> </thead> <tbody> <tr> <td>7.44</td> </tr> </tbody> </table> <br /> ### MAX() The {{%badge%}}MAX(){{%/badge%}} function returns the maximum value of the selected column. You can use it to return the largest value of a column that contains numerical data or the record that comes last in the alphabetical order.<br /> The basic syntax for using a {{%badge%}}MAX(){{%/badge%}} function with the {{%badge%}}SELECT{{%/badge%}} statement is:<br /> <br /> {{%code class="language-sql"%}}SELECT MAX(column_name) FROM base_table_name {{%/code%}} {{%bold%}}Example{{%/bold%}}:<br /> To view the most expensive movie from the _Pricing_ table, execute the following query:<br /> {{%code class="language-sql"%}}SELECT MAX(Price) FROM Pricing {{%/code%}} This will generate the following output:<br /> <table class="content-table"> <thead> <tr> <th><strong>MAX(Price)</strong></th> </tr> </thead> <tbody> <tr> <td>11.50</td> </tr> </tbody> </table> <br /> ### COUNT() The {{%badge%}}COUNT(){{%/badge%}} function returns the value for the number of rows in the records that are returned for a particular column in the output. The {{%badge%}}COUNT{{%/badge%}} function is also used with the {{%badge%}}SELECT{{%/badge%}} statement. The basic syntax for using a {{%badge%}}COUNT(){{%/badge%}} function with the {{%badge%}}SELECT{{%/badge%}} statement is: {{%code class="language-sql"%}}SELECT COUNT(column_name) FROM base_table_name{{%/code%}} **Example:** To view the number of theaters in New York City and Albany, from the _Theaters_ table, execute the following query:<br /> {{%code class="language-sql"%}}SELECT COUNT(TheaterName) FROM Theaters WHERE Location='New York City' OR Location='Albany'{{%/code%}} This will generate the following output:<br /> <table class="content-table"> <thead> <tr> <th><strong>COUNT(TheaterName)</strong></th> </tr> </thead> <tbody> <tr> <td>2</td> </tr> </tbody> </table> <br /> ### SUM() The {{%badge%}}SUM(){{%/badge%}} function calculates and returns the total sum of a numeric column's records. This can only be used for a column that contains numerical data. The {{%badge%}}SUM(){{%/badge%}} function can also be used with the {{%badge%}}SELECT{{%/badge%}} statement for a singular column.<br /> The basic syntax for using a {{%badge%}}SUM(){{%/badge%}} function with the {{%badge%}}SELECT{{%/badge%}} statement is:<br /> {{%code class="language-sql"%}}SELECT SUM(column_name) FROM base_table_name{{%/code%}} {{%bold%}}Example{{%/bold%}}:<br /> To view the sum total of the ticket charges for 'The First Purge' and 'Hotel Transylvania 3: Summer Vacation' from the _Pricing_ table, execute the following query:<br /> {{%code class="language-sql"%}}SELECT SUM(Price) FROM Pricing WHERE MovieID='2056' OR MovieID='2058'{{%/code%}} This will generate the following output:<br /> <table class="content-table"> <thead> <tr> <th><strong>SUM(Price)</strong></th> </tr> </thead> <tbody> <tr> <td>20.7</td> </tr> </tbody> </table> <br /> ### AVG() The {{%badge%}}AVG(){{%/badge%}} function calculates and returns the average value for a numeric column's records. This can only be used for a column that contains numerical data. The {{%badge%}}AVG(){{%/badge%}} function is used along with the {{%badge%}}SELECT{{%/badge%}} statement for a particular column alone. The {{%badge%}}AVG (){{%/badge%}} ZCQL function can be applied on the following data types: - **Date** - **DateTime** - **Boolean** {{%note%}}{{%bold%}}Note:{{%/bold%}} * This functionality is only available in {{%bold%}}ZCQL V2{{%/bold%}}. * From {{%bold%}}December 01st, 2024{{%/bold%}}, all your current projects in all your {{%link href="/en/getting-started/catalyst-organizations/" %}}Orgs{{%/link%}} present in the {{%link href="/en/deployment-and-billing/environments/development-environment/" %}}Development Environment{{%/link%}} will be automatically mapped to {{%bold%}}ZCQL V2 Parser{{%/bold%}}. * From {{%bold%}}April 01st, 2025{{%/bold%}}, all the projects present in all {{%italics%}}Orgs{{%/italics%}} that have already been mapped to {{%bold%}}ZCQL V2 Parser{{%/bold%}} in {{%italics%}}Development Environment{{%/italics%}} will be automatically mapped to {{%bold%}}ZCQL V2 Parser{{%/bold%}} in the {{%link href="/en/deployment-and-billing/environments/production-environment/" %}}Production Environment{{%/link%}}, if and when production is enabled for the project.{{%/note%}} The basic syntax for using a {{%badge%}}AVG(){{%/badge%}} function with the {{%badge%}}SELECT{{%/badge%}} statement is:<br /> {{%code class="language-sql"%}}SELECT AVG(column_name) FROM base_table_name{{%/code%}} {{%bold%}}Example{{%/bold%}}:<br /> To view the average ticket price for the movies in the _Pricing_ table, execute the following query:<br /> {{%code class="language-sql"%}}SELECT AVG(Price) As AverageTicketPrice FROM Pricing {{%/code%}} This will generate the following output: <table class="content-table"> <thead> <tr> <th><strong>AVG(Price)</strong></th> </tr> </thead> <tbody> <tr> <td>9.19</td> </tr> </tbody> </table> <br /> ### DISTINCT The {{%badge%}}DISTINCT{{%/badge%}} statement returns only the distinct record values for a column. If there are duplicate values in a column, this statement will not return the duplicates. The {{%badge%}}DISTINCT{{%/badge%}} statement is used along with the {{%badge%}}SELECT{{%/badge%}} statement in a query. The basic syntax for using a {{%badge%}}SELECT DISTINCT{{%/badge%}} statement is:<br /> {{%code class="language-sql"%}}SELECT DISTINCT column_name(s) FROM base_table_name{{%/code%}} {{%bold%}}Example{{%/bold%}}:<br /> To view the distinct theaters the movies are being screened at, execute the following query:<br /> {{%code class="language-sql"%}}SELECT DISTINCT Theaters.TheaterName FROM Movies INNER JOIN Theaters ON Movies.TheaterID=Theaters.TheaterID{{%/code%}} This will generate the following output:<br /> <table class="content-table"> <thead> <tr> <th><strong>TheaterName</strong></th> </tr> </thead> <tbody> <tr> <td>The Express Cinemas</td> </tr> <tr> <td>Cosmos Theater</td> </tr> <tr> <td>FunTime Cinemas</td> </tr> </tbody> </table> ### Use Multiple ZCQL Functions You can use multiple ZCQL functions on the same column in a single ZCQL query statement. **Example Database**: The employee details of Zylker Technologies are being maintained in the {{%link href="/en/cloud-scale/help/data-store/introduction/" %}}Data Store{{%/link%}} in the *Zylker_Employee_DB* table. The table contains the following columns and rows: <table class="content-table"> <thead> <tr> <th class="w15p">ID</th> <th class="w30p">Name</th> <th class="w30p">Department</th> <th class="w25p">Salary</th> </tr> </thead> <tbody> <tr> <td>ZT-001</td> <td>Amelia Burrows</td> <td>Product Management</td> <td>15000</td> </tr> <tr> <td>ZT-2001</td> <td>Bruce Wayne</td> <td>Sr. Management</td> <td>85000</td> </tr> <tr> <td>ZT-239</td> <td>Clark Kane</td> <td>Media Relations</td> <td>4500</td> </tr> <tr> <td>ZT-4289</td> <td>Michelle Mascarenhas</td> <td>Finance</td> <td>89000</td> </tr> </tbody> </table> Next, we apply multiple ZCQL functions together in the query, as shown below: {{%code class="language-sql"%}}SELECT MIN (Salary), MAX (Salary), COUNT (Salary), SUM (Salary), AVG (Salary) FROM Zylker_Employee_DB{{%/code%}} <table class="content-table"> <thead> <tr> <th class="w20p">MAX(Salary)</th> <th class="w20p">SUM(Salary)</th> <th class="w20p">MIN(Salary)</th> <th class="w20p">AVG(Salary)</th> <th class="w20p">COUNT(Salary)</th> </tr> </thead> <tbody> <tr> <td>89000</td> <td>193500</td> <td>4500</td> <td>48975</td> <td>4</td> </tr> </tbody> </table> {{%note%}}{{%bold%}}Note:{{%/bold%}} * This functionality is only available in {{%bold%}}ZCQL V2{{%/bold%}}. * From {{%bold%}}December 01st, 2024{{%/bold%}}, all your current projects in all your {{%link href="/en/getting-started/catalyst-organizations/" %}}Orgs{{%/link%}} present in the {{%link href="/en/deployment-and-billing/environments/development-environment/" %}}Development Environment{{%/link%}} will be automatically mapped to {{%bold%}}ZCQL V2 Parser{{%/bold%}}. * From {{%bold%}}April 01st, 2025{{%/bold%}}, all the projects present in all {{%italics%}}Orgs{{%/italics%}} that have already been mapped to {{%bold%}}ZCQL V2 Parser{{%/bold%}} in {{%italics%}}Development Environment{{%/italics%}} will be automatically mapped to {{%bold%}}ZCQL V2 Parser{{%/bold%}} in the {{%link href="/en/deployment-and-billing/environments/production-environment/" %}}Production Environment{{%/link%}}, if and when production is enabled for the project.{{%/note%}} -------------------------------------------------------------------------------- title: "ZCQL V2 Syntax and Exceptions" description: "ZCQL is Catalyst's own query language modelled after familiar query languages that you can use to query your app's database efficiently." last_updated: "2026-03-18T07:41:08.537Z" source: "https://docs.catalyst.zoho.com/en/cloud-scale/help/zcql/syntax-exceptions/" service: "Cloud Scale" -------------------------------------------------------------------------------- # ZCQL V2 Syntax and Exceptions Catalyst has added several syntax upgrades and enhancements to help you write ZCQL queries more effectively. These new syntax upgrades will provide additional functionalities and enable you to perform data related operations in the Data Store with much ease. {{%note%}}{{%bold%}}Note:{{%/bold%}}<br /> * To use these feature enhancements, you will need to migrate to the {{%bold%}}V2{{%/bold%}} version. The syntax upgrades will not be applicable if you implement them while executing ZCQL commands in {{%bold%}}V1{{%/bold%}}. * From {{%bold%}}December 01st, 2024{{%/bold%}}, all your current projects in all your {{%link href="/en/getting-started/catalyst-organizations/" %}}Orgs{{%/link%}} present in the {{%link href="/en/deployment-and-billing/environments/development-environment/" %}}Development Environment{{%/link%}} will be automatically mapped to {{%bold%}}ZCQL V2 Parser{{%/bold%}}. * From {{%bold%}}April 01st, 2025{{%/bold%}}, all the projects present in all {{%italics%}}Orgs{{%/italics%}} that have already been mapped to {{%bold%}}ZCQL V2 Parser{{%/bold%}} in {{%italics%}}Development Environment{{%/italics%}} will be automatically mapped to {{%bold%}}ZCQL V2 Parser{{%/bold%}} in the {{%link href="/en/deployment-and-billing/environments/production-environment/" %}}Production Environment{{%/link%}}, if and when production is enabled for the project.{{%/note%}} The following exception and syntax nuances need to be kept in mind when you execute a ZCQL query: 1. The {{%link href="/en/cloud-scale/help/zcql/zcql-functions/#avg" %}}{{%badge%}}AVG (){{%/badge%}}{{%/link%}} ZCQL function can be applied on the following data types: - **Date** - **DateTime** - **Boolean** 2. When you are indicating a value in your ZCQL statement, indicate the value in single quotes.<br /> For example, if you have to fetch a particular name from an employee database, you will write the command in the following manner: {{%code class="language-sql"%}}SELECT Name FROM Employee_DB WHERE name = 'Amelia'{{%/code%}} 3. If a column is of Boolean data type: - No string values are permitted. - Boolean data type will only support the following values: - FALSE - TRUE - NULL 4. If you are using an **Encrypted datatype**, then the following ZCQL functions cannot be used on that column: - {{%badge%}}AVG(){{%/badge%}} - {{%badge%}}SUM(){{%/badge%}} - {{%badge%}}MAX(){{%/badge%}} - {{%badge%}}MIN(){{%/badge%}} 5. {{%link href="/en/cloud-scale/help/zcql/syntax-exceptions/#list-of-zcql-exceptions" %}}Appropriate exceptions{{%/link%}} have been added when you use comparative operators like {{%badge%}}Less Than{{%/badge%}}, {{%badge%}}Greater Than{{%/badge%}}, {{%badge%}}Less Than or Equal{{%/badge%}}, and {{%badge%}}Greater Than or Equal{{%/badge%}}, on {{%bold%}}Encrypted Text{{%/bold%}}, and {{%bold%}}Boolean{{%/bold%}} data type. These exceptions are not applicable for {{%badge%}}Equal{{%/badge%}} and {{%badge%}}Not Equal{{%/badge%}} operators. 6. Subqueries are now supported in {{%badge%}}WHERE{{%/badge%}} clause statements. You can find out more about them from this {{%link href="/en/cloud-scale/help/zcql/where/#subqueries-in-where-clause" %}}help document{{%/link%}}. 7. {{%link href="/en/cloud-scale/help/zcql/groupby-orderby/#order-by-statement" %}}{{%badge%}}ORDER BY{{%/badge%}}{{%/link%}} clause now supports the use of {{%link href="/en/cloud-scale/help/zcql/zcql-functions/" %}}ZCQL functions{{%/link%}}. 8. In all columns where you use the {{%badge%}}ORDER BY{{%/badge%}} clause, the {{%badge%}}ASC{{%/badge%}} and {{%badge%}}DESC{{%/badge%}} functionality can now be applied. You can also apply {{%badge%}}ASC{{%/badge%}} and {{%badge%}}DESC{{%/badge%}} for individual columns. **Example Database**:<br /> Consider the following database - **ZylkerEmployeeCompensation** <table class="content-table"> <thead> <tr> <th class="w50p">Name</th> <th class="w25p">Salary</th> <th class="w25p">Reimbursement</th> </tr> </thead> <tbody> <tr> <td>Michelle Mascarenhas</td> <td>90000</td> <td>10000</td> </tr> <tr> <td>Bruce Kyle</td> <td>3000000</td> <td>45000</td> </tr> <tr> <td>Clark Kane</td> <td>3300</td> <td>1500</td> </tr> <tr> <td>Judith Bridges</td> <td>4700</td> <td>300</td> </tr> <tr> <td>Devon Dreymond</td> <td>80000</td> <td>2300</td> </tr> <tr> <td>Ashley Kim</td> <td>6000</td> <td>200</td> </tr> </tbody> </table> Execute the following command to list the table {{%code class="language-sql"%}}SELECT Salary, Reimbursement FROM ZylkerEmployeeCompensation ORDER BY Salary ASC, Reimbursement DESC{{%/code%}} **Output:** <br /> <table class="content-table"> <thead> <tr> <th class="w50p">ZylkerEmployeeCompensation.Salary</th> <th class="w50p">ZylkerEmployeeCompensation.Reimbursement</th> </tr> </thead> <tbody> <tr> <td>3300</td> <td>45000</td> </tr> <tr> <td>4700</td> <td>10000</td> </tr> <tr> <td>6000</td> <td>2300</td> </tr> <tr> <td>80000</td> <td>1500</td> </tr> <tr> <td>90000</td> <td>300</td> </tr> <tr> <td>3000000</td> <td>200</td> </tr> </tbody> </table> 9. {{%link href="/en/cloud-scale/help/zcql/syntax-exceptions/#list-of-zcql-exceptions" %}}Appropriate exceptions{{%/link%}} will be triggered in the following cases: - If the permitted max/min value has been exceeded in **Integer** datatype. - If the character length is longer than the value set by you in **VarChar** datatype. - If the value is beyond the supported range in **BIGINT** datatype. 10. You can write **four** {{%link href="/en/cloud-scale/help/zcql/joins/" %}}{{%badge%}}JOIN{{%/badge%}}{{%/link%}} clauses in a single ZCQL statement. However, you can only write **one** {{%badge%}}JOIN{{%/badge%}} condition for each join clause. 11. **Table Alias** functionality is now supported in ZCQL. You can now invoke alias functionality in ZCQL using the {{%badge%}}AS{{%/badge%}} ZCQL command. Using this command you can refer to your table with other names. **Example Database**:<br /> Consider the following databases: **Table 1: ZylkerEmployeeCompensation** <table class="content-table"> <thead> <tr> <th class="w50p">Name</th> <th class="w25p">Salary</th> <th class="w25p">Reimbursement</th> </tr> </thead> <tbody> <tr> <td>Michelle Mascarenhas</td> <td>90000</td> <td>10000</td> </tr> <tr> <td>Bruce Kyle</td> <td>3000000</td> <td>45000</td> </tr> <tr> <td>Clark Kane</td> <td>3300</td> <td>1500</td> </tr> <tr> <td>Judith Bridges</td> <td>4700</td> <td>300</td> </tr> <tr> <td>Devon Dreymond</td> <td>80000</td> <td>2300</td> </tr> <tr> <td>Ashley Kim</td> <td>6000</td> <td>200</td> </tr> </tbody> </table> **Table 2: ZylkerEmployeeRelation** <table class="content-table"> <thead> <tr> <th class="w50p">Manager</th> <th class="w50p">Member</th> </tr> </thead> <tbody> <tr> <td>Michelle Mascarenhas</td> <td>Bruce Kyle</td> </tr> <tr> <td>Michelle Mascarenhas</td> <td>Clark Kane</td> </tr> <tr> <td>Judith Bridges</td> <td>Michelle Mascarenhas</td> </tr> </tbody> </table> Execute the following command to list the table {{%code class="language-sql"%}}SELECT Managers.Name, Members.Name FROM ZylkerEmployeeCompensation AS Managers INNER JOIN ZylkerEmployeeRelation AS asso ON Managers.ROWID = asso.Manager INNER JOIN ZylkerEmployeeCompensation AS Members ON Members.ROWID = asso.Member{{%/code%}} **Output:** <br/> <table class="content-table"> <thead> <tr> <th class="w50p">Manager.Name</th> <th class="w50p">Member.Name</th> </tr> </thead> <tbody> <tr> <td>Michelle Mascarenhas</td> <td>Bruce Kyle</td> </tr> <tr> <td>Michelle Mascarenhas</td> <td>Clark Kane</td> </tr> <tr> <td>Judith Bridges</td> <td>Michelle Mascarenhas</td> </tr> </tbody> </table> {{%note%}}{{%bold%}}Note:{{%/bold%}} The table Alias operation is only supported for {{%badge%}}**SELECT**{{%/badge%}} statements.{{%/note%}} 12. You can check if you have a NULL value by using the **IS/IS NOT** operator.The **IS/IS NOT** operator support is only available for NULL values. 13. When writing {{%link href="/en/cloud-scale/help/zcql/groupby-orderby/" %}}{{%badge%}}GROUP BY{{%/badge%}}{{%/link%}} with {{%link href="/en/cloud-scale/help/zcql/groupby-orderby/#binaryof-function" %}}{{%badge%}}Binaryof(){{%/badge%}}{{%/link%}} statements, the following ZCQL rules need to be remembered: - The Binaryof() functionality can only be used in GROUP BY statements. - Binaryof() can only be used on columns with VarChar or Text datatype. - When Binaryof() is used in a GROUP BY statement, the returned values are case-sensitive. For example, consider you have the values 'AMELIA', 'amelia', 'Burrows', and 'burrows' in a column called Name, in a table called Zylker_EMP. If you use the following command:<br /> {{%code class="language-sql"%}}Select Name from Zylker_EMP group by binaryof(Name){{%/code%}} The result could be any of the following:<br /> - AMELIA - amelia - burrows - Burrows 14. Column names can now contain numerical values. You can signify such column names in the query using a backtick (**`**) punctuation. For example, a {{%badge%}}SELECT{{%/badge%}} query on a table called Numbers with a column named 01 can be written in the following manner: {{%code class="language-sql"%}}SELECT `01` FROM Numbers{{%/code%}}<br /> ### List of ZCQL Exceptions The following is a list of common exceptions and example query statements that can trigger these exceptions. Ensure that you go through the list, and follow ZCQL syntax strictly to avoid triggering these exceptions. <table class="content-table"> <thead> <tr> <th class="w15p">Datatype</th> <th class="w20p">Case</th> <th class="w25p">Sample ZCQL Query</th> <th class="w40p">Triggered Exception</th> </tr> </thead> <tbody> <tr> <td rowspan="5"><strong>Encrypted Text</strong></td> <td>Certain operators are not supported</td> <td>SELECT * FROM temp WHERE enc > 'temsp'</td> <td>{{%badge%}}Operator > is not supported for<br />ENCRYPTED TEXT{{%/badge%}} The same exception applies, but the exception message will be altered accordingly if you use these operators **<**, **<=**, **>=** in the ZCQL statement</td> </tr> <tr> <td>{{%badge%}}LIKE{{%/badge%}} is not supported</td> <td>SELECT * FROM temp WHERE enc LIKE '*temsp'</td> <td>{{%badge%}}Operator LIKE is not supported for<br />ENCRYPTED TEXT{{%/badge%}} The same exception applies, but the exception message will be altered accordingly if you use **NOT LIKE** in the ZCQL statement</td> </tr> <tr> <td>{{%badge%}}BETWEEN{{%/badge%}} is not supported</td> <td>SELECT * FROM temp WHERE enc BETWEEN 'one' AND 'ten'</td> <td>{{%badge%}}Operator BETWEEN is not supported for<br />ENCRYPTED TEXT{{%/badge%}} The same exception applies, but the exception message will be altered accordingly if you use **NOT BETWEEN** in the ZCQL statement</td> </tr> <tr> <td>{{%badge%}}IN{{%/badge%}} is not supported</td> <td>SELECT * FROM temp WHERE enc IN ('one','three')</td> <td>{{%badge%}}Operator IN is not supported for<br />ENCRYPTED TEXT{{%/badge%}} The same exception applies if you use **NOT IN** in your query, but the exception message will be altered accordingly</td> </tr> <tr> <td>ZCQL functions is not supported</td> <td>SELECT AVG(enc) FROM temp</td> <td>{{%badge%}}AVG() function is not supported for<br />ENCRYPTED_TEXT{{%/badge%}} {{%link href="/en/cloud-scale/help/zcql/zcql-functions/" %}}ZCQL Functions{{%/link%}} are not supported in **Encrypted Text**.</td> </tr> <tr> <td rowspan="4"><strong>Boolean</strong></td> <td>Certain operators are not supported</td> <td>SELECT * FROM temp WHERE bools > FALSE</td> <td>{{%badge%}}Operator > is not supported for BOOLEAN{{%/badge%}} The same exception applies, but the exception message will be altered accordingly if you use these operators **<**, **<=**, **>=** in the ZCQL statement</td> </tr> <tr> <td>{{%badge%}}LIKE{{%/badge%}} is not supported</td> <td>SELECT * FROM temp WHERE bools LIKE FALSE</td> <td>{{%badge%}}Operator LIKE is not supported for BOOLEAN{{%/badge%}} The same exception applies if you use **NOT LIKE** in your query, but the exception message will be altered accordingly</td> </tr> <tr> <td>{{%badge%}}BETWEEN{{%/badge%}} is not supported</td> <td>SELECT * FROM temp WHERE bools BETWEEN TRUE and FALSE</td> <td>{{%badge%}}Operator BETWEEN is not supported for<br />BOOLEAN{{%/badge%}} The same exception applies if you use **NOT BETWEEN** in your query, but the exception message will be altered accordingly</td> </tr> <tr> <td>{{%badge%}}IN{{%/badge%}} is not supported</td> <td>SELECT * FROM temp WHERE bools IN (TRUE, FALSE)</td> <td>{{%badge%}}Operator IN is not supported for BOOLEAN{{%/badge%}} The same exception applies if you use **NOT IN** in your query, but the exception message will be altered accordingly</td> </tr> <tr> <td rowspan="1"><strong>VarChar</strong></td> <td>Maximum length exceeded</td> <td>INSERT INTO temp (Fishes) value ('salmon')</td> <td>{{%badge%}}'salmon' data too long for column 'Fishes'{{%/badge%}} The column {{%badge%}}Fishes{{%/badge%}} was created as a **VarChar** and the **MaxLength** was set as {{%badge%}}5{{%/badge%}}. In this statement, {{%badge%}}'Salmon'{{%/badge%}} exceeds the **MaxLength**</td> </tr> <tr> <td rowspan="1"><strong>Integer</strong></td> <td>Maximum value exceeded</td> <td>INSERT INTO temp (numbers) VALUE (546895326908)</td> <td>{{%badge%}}For column 'ints' INT value should be between<br />-9999999999 and 9999999999{{%/badge%}} The column numbers was created as **INT**. The value of **INT** datatype should be between {{%badge%}}-9999999999{{%/badge%}} and {{%badge%}}9999999999{{%/badge%}}</td> </tr> <tr> <td rowspan="1"><strong>Big Integer</strong></td> <td>Maximum value exceeded</td> <td>INSERT INTO temp (numbers) VALUE (92343432412351<br />435123453245)</td> <td>{{%badge%}}Given numeric value 92343432412351<br />435123453 is too large{{%/badge%}} The column numbers was created as **BIGINT**. The value of **BIGINT** datatype should be between {{%badge%}}-9223372036854775808{{%/badge%}} and {{%badge%}}9223372036854775807{{%/badge%}}</td> </tr> <tr> <td rowspan="1"><strong>Text</strong></td> <td>Value not enclosed in single quotes</td> <td>SELECT * FROM temp WHERE name = one</td> <td>{{%badge%}}Unknown Table temp or Unknown Column<br />one in WHERE{{%/badge%}} The value {{%badge%}}one{{%/badge%}} will be considered as column if it is not enclosed in quotes-'{{%badge%}}one{{%/badge%}}'</td> </tr> </tbody> </table> -------------------------------------------------------------------------------- title: "The ZCQL Console" description: "ZCQL is Catalyst's own query language modelled after familiar query languages that you can use to query your app's database efficiently." last_updated: "2026-03-18T07:41:08.537Z" source: "https://docs.catalyst.zoho.com/en/cloud-scale/help/zcql/zcql-console/" service: "Cloud Scale" -------------------------------------------------------------------------------- # The ZCQL Console The **ZCQL Console** is an intuitive tool that allows you to execute your required ZCQL queries, perform data manipulation operations on data stored in the {{%link href="/en/cloud-scale/help/data-store/introduction/" %}}Data Store{{%/link%}}, and save your required ZCQL queries directly in the Catalyst console. ### Feature Highlights <br /> You can use the *ZCQL Console* provides you with the following offerings: * When you type in a query, you are provided with a relevant and meaningful code completion suggestion that allows you to form your queries easily and quickly. * Execute any query in the console, and you can directly view the complete output. * Copy the query using the *copy button* and apply it to your code with ease. * Save any of your required ZCQL queries and reuse it when required. * Using the **ZCQL Explorer**, you can easily search for and reuse the required saved query, view column information of all the tables created in the project, and also reuse previously executed queries from the *History* section. {{%note%}}{{%bold%}}Note:{{%/bold%}} * By default, only {{%bold%}}50 ZCQL queries{{%/bold%}} can be saved in a single Catalyst project. If you have a higher requirement, you can contact the {{%link href="mailto:support@zohocatalyst.com" %}}Catalyst Support team{{%/link%}} to increase this threshold. * Only the latest {{%bold%}}100 ZCQL queries{{%/bold%}} will be logged in the {{%italics%}}History{{%/italics%}} section of the {{%bold%}}ZCQL Explorer{{%/bold%}}. However, there is no time limit on how long the query will be available in the {{%italics%}}History{{%/italics%}}.{{%/note%}} ### Access the ZCQL Console To access the ZCQL Console:<span></span> 1. Navigate to the **Data Store** component present under the *STORAGE* section of the {{%link href="/en/cloud-scale/" %}}CloudScale service{{%/link%}}. <br /> {{%note%}}{{%bold%}}Note:{{%/bold%}} You need to {{%link href="/en/cloud-scale/help/data-store/tables/#create-a-new-table" %}}create a table{{%/link%}} in the {{%bold%}}Data Store{{%/bold%}} to be access the {{%bold%}}ZCQL Console{{%/bold%}}.{{%/note%}} 2. Click the **ZCQL Console** tab. <br /> You can type your query in this console and perform the required operations. ### The ZCQL Explorer The *ZCQL Explorer* is an expandable and collapsible feature of the ZCQL console. It contains the following sections: * **Schema**: Schema contains information about the table that includes the list of columns present in the table, if the value for the columns is mandatory, and the datatype of the data that will be stored in these columns. <br /> * **Saved Queries**: This section will contain a list of all the queries you have saved using the **Save Query** button. You can search for your required query using its name and reuse it. <br /> {{%note%}}{{%bold%}}Note:{{%/bold%}} By default, only {{%bold%}}50 ZCQL queries{{%/bold%}} can be saved in a single Catalyst project. If you have a higher requirement, you can contact the {{%link href="mailto:support@zohocatalyst.com" %}}Catalyst Support team{{%/link%}} to increase this threshold.{{%/note%}} * **History**: The *History* section will contain the complete list of the **latest 100 queries** that have been executed in the console. <br /> If you wish to save a query from the *History* section, click the **save-icon** next to the required query and follow the steps listed in this [section](#SaveaZCQLQuery). {{%note%}}{{%bold%}}Note{{%/bold%}}: Only the latest {{%bold%}}100 ZCQL queries{{%/bold%}} will be present in the {{%italics%}}History{{%/italics%}} section. However, there is no time limit on how long the query can be stored in {{%italics%}}History{{%/italics%}}.{{%/note%}} ### Execute a ZCQL Query To execute a ZCQL query in the ZCQL Console: 1. Enter your ZCQL query in the console. <br /> 2. Click the **Execute Query** button. <br /> You will be able to view the output directly in the console. The query will be executed and logged in the *History* section present in the **ZCQL Explorer**. By default, you will be viewing the output in *Table View*. You also have the option to view the output as a JSON by clicking the **View** drop-down, and selecting the **JSON View** option. <br /> You also have the option to copy this JSON and use it as required. ### Save a ZCQL Query In the ZCQL Console, you can execute your required queries and save them. Saving your queries allows you to retrieve them easily and use them whenever they are required instead of manually inputting them for each session. {{%note%}}{{%bold%}}Note:{{%/bold%}} By default, only {{%bold%}}50 ZCQL queries{{%/bold%}} can be saved in a single Catalyst project. If you have a higher requirement, you can contact the {{%link href="mailto:support@zohocatalyst.com" %}}Catalyst Support team{{%/link%}} to increase this threshold.{{%/note%}} To save a ZCQL query: 1. Enter your required query, and click the **Save Query** button. <br /> 2. In the pop-up, go through the *Query Preview* section and provide a **Query Name** to refer your query by and click **Save**. <br /> Your query will be saved. You can view and manage your queries from the *ZCQL Explorer* section, under the **Saved Queries** tab. <br /> You can click the *Console* button to print the query into your console. <br /> You can also further edit this query and execute it as needed or save it under a different query name by repeating the steps mentioned above. ### Delete a Saved Query To delete a saved query: 1. Click the **delete-icon** present next to the required query in the **Saved Queries** section of the *ZCQL Explorer*. <br /> 2. Click **Yes, Proceed** on the confirmation pop-up to delete the required query. <br /> The query will be deleted. ### Using ZCQL V2 in Function Code To use **ZCQL V2** code in your function, you will need to set an environment variable for it in the following manner: 1. Navigate to *Serverless* > **Functions**. <br /> 2. Click the function that contains your ZCQL V2 code. 3. Click the **Configuration** tab, then click **Create Variable** under *Environmental Variables*. <br /> 4. Enter the following values in the respective fields:<br />**Key**: {{%badge%}}ZOHO_CATALYST_ZCQL_PARSER{{%/badge%}}<br />**Development Value**: {{%badge%}}V2{{%/badge%}} <br /> 5. Click **Save**. <br /> You will now be able to use ZCQL V2 in your function code. {{%note%}}{{%bold%}}Note:{{%/bold%}} * You do not have to reference the environment variable anywhere in your code. Once you have configured it in the console, you can use {{%bold%}}ZCQL V2{{%/bold%}} in your function code. * From {{%bold%}}December 01st, 2024{{%/bold%}}, all your current projects in all your {{%link href="/en/getting-started/catalyst-organizations/" %}}Orgs{{%/link%}} present in the {{%link href="/en/deployment-and-billing/environments/development-environment/" %}}Development Environment{{%/link%}} will be automatically mapped to {{%bold%}}ZCQL V2 Parser{{%/bold%}}. * From {{%bold%}}April 01st, 2025{{%/bold%}}, all the projects present in all {{%italics%}}Orgs{{%/italics%}} that have already been mapped to {{%bold%}}ZCQL V2 Parser{{%/bold%}} in {{%italics%}}Development Environment{{%/italics%}} will be automatically mapped to {{%bold%}}ZCQL V2 Parser{{%/bold%}} in the {{%link href="/en/deployment-and-billing/environments/production-environment/" %}}Production Environment{{%/link%}}, if and when production is enabled for the project.{{%/note%}} --- ## API Reference — ZCQL -------------------------------------------------------------------------------- title: "Execute ZCQL Query" description: "This API enables you to execute a ZCQL query to retrieve, insert, update, or delete data from the tables in the Data Store." last_updated: "2026-03-18T07:41:08.545Z" source: "https://docs.catalyst.zoho.com/en/api/code-reference/cloud-scale/zcql/execute-zcql-query/" service: "Cloud Scale" -------------------------------------------------------------------------------- {{% section path="/en/api/code-reference/cloud-scale/zcql/execute-zcql-query/" id="ExecuteZCQLQuery" %}} {{%divtag class="LeftContent"%}} # ZCQL {{%link href="/en/cloud-scale/help/zcql/introduction/"%}}ZCQL{{%/link%}} is Catalyst's own query language that enables you to perform data retrieval, insertion, updating, and deletion operations on the tables in the {{%link href="/en/cloud-scale/help/data-store/introduction/"%}}Catalyst Data Store{{%/link%}}. You can execute a variety of DML queries using ZCQL to obtain or manipulate data, and use various clauses and statements such as the SQL Join clauses, Groupby and OrderBy statements, and built-in SQL functions. # Execute a ZCQL Query ### Description This API enables you to execute a ZCQL query to retrieve, insert, update, or delete data from the tables in the Data Store. You can execute this API by passing a ZCQL query in the JSON request, as shown in the sample requests. {{%note%}} {{%bold class="bold-primary"%}}Note:{{%/bold%}} The API request format is the same for all ZCQL query execution operations.{{%/note%}} ### Request Details #### Request URL {{%request class="apiyellow" req="POST" %}}<p>{api-domain}/baas/v1/project/{project_id}/query</p>{{%/request%}} {{%request_id id="api-domain" data="URL" %}}The DC-specific domain to access Catalyst API{{%/request_id%}} {{%request_id id="project_id" data="Numerical" %}}The {{%link href="/en/getting-started/set-up-a-catalyst-project/general-settings/"%}}unique ID{{%/link%}} of the project{{%/request_id%}} #### Request Headers {{%scope%}} **Authorization:** Zoho-oauthtoken {oauth_token} <br> **Content-Type:** application/json {{%/scope%}} **Optional Headers** <br> {{%scope%}} **CATALYST-ORG:** {org_id} {{%/scope%}} {{%scope%}} **Environment:** Development {{%/scope%}} #### Scope {{%scope%}} ZohoCatalyst.zcql.CREATE {{%/scope%}} {{%note%}} {{%bold class="bold-primary"%}}Note:{{%/bold%}} This operation can also be executed with Catalyst user authentication permissions using Catalyst SDKs. Refer to the [Catalyst API Prerequisites section](/en/api/introduction/overview-and-prerequisites/#prerequisites) for details.{{%/note%}} #### Request JSON Properties {{% nested_fields children="open" border="yes" expandable="no" title="query" textformat="string" condition="mandatory" maxlength=" " %}} The ZCQL query to be executed {{% /nested_fields %}} ### Response Details Data Retrieval Operations: The API returns the records that were fetched as a result of the query execution as the response. Data Insertion and Updating Operations: The API returns the records that were inserted or updated through the query as the response. Data Deletion Operations: The API returns the deleted row count. {{%/divtag%}} {{%divtag class="RightContent"%}} {{% panel_with_adjustment header="Sample Request: Data Retrieval" class="language-bash" %}}curl -X POST \ https://api.catalyst.zoho.com/baas/v1/project/4000000006007/query \ -H "Authorization: Zoho-oauthtoken 1000.910*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*16.2f*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*57" \ -H "Content-Type: application/json" \ -d '{ "query":"SELECT EmpID, EmpName, Department, Address from EmpDetails ORDER BY EmpID ASC LIMIT 1000,5" }' {{% /panel_with_adjustment %}} {{% panel_with_adjustment header="Sample Response: Data Retrieval" class="language-json line-numbers"%}} { "status": "success", "data": [ { "EmpDetails": { "CREATORID": "3376000000002003", "MODIFIEDTIME": "2023-06-05 15:09:33:881", "CREATEDTIME": "2023-06-05 15:09:33:881", "ROWID": "3376000001924975", "EmpID": "1001", "EmpName": "Allison Powell", "Department":"Marketing", "Address": "13, Winter Avenue, Philadelphia, PY" } }, { "EmpDetails": { "CREATORID": "3376000000002003", "MODIFIEDTIME": "2023-06-05 15:09:33:881", "CREATEDTIME": "2023-06-05 15:09:33:881", "ROWID": "3376000001927682", "EmpID": "1002", "EmpName": "James Cortez", "Department":"HR", "Address": "25, Blossom Street, Austin, TX" } }, { "EmpDetails": { "CREATORID": "3376000000002003", "MODIFIEDTIME": "2023-06-02 21:04:11:111", "CREATEDTIME": "2023-06-02 15:05:21:001", "ROWID": "3376000001224112", "EmpID": "1003", "EmpName": "Han Chan", "Department":"Sales", "Address": "112, St.Patrick's Road, Louisville, KY" } }, { "EmpDetails": { "CREATORID": "337600000003111", "MODIFIEDTIME": "2023-06-02 21:04:11:111", "CREATEDTIME": "2023-06-02 15:05:21:001", "ROWID": "3376000001242012", "EmpID": "1004", "EmpName": "Rubella Miguel", "Department":"Accounts", "Address": "333, Marine Bay, Salt Lake City, UT" } }, { "EmpDetails": { "CREATORID": "3376000000115254", "MODIFIEDTIME": "2023-04-12 21:04:10:521", "CREATEDTIME": "2023-06-11 15:05:02:541", "ROWID": "3376000001241341", "EmpID": "1005", "EmpName": "Ronwick Boseman", "Department":"Support", "Address": "61, Gringott's Avenue, Herfordshire, CO" } } ] }{{% /panel_with_adjustment %}} {{% panel_with_adjustment header="Sample Request: Data Insertion" class="language-bash" %}}curl -X POST \ https://api.catalyst.zoho.com/baas/v1/project/4000000006007/query \ -H "Authorization: Zoho-oauthtoken 1000.910*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*16.2f*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*57 \ -H "Content-Type: application/json" \ -d '{ "query":"INSERT INTO EmpDetails VALUES (1007,'Randy Marsh','Sales','42, Tenth Street, Jacksonville, FL', '14 MAR 2017')" }' {{% /panel_with_adjustment %}} {{% panel_with_adjustment header="Sample Response: Data Insertion" class="language-json line-numbers"%}} { "status": "success", "data": [ { "EmpDetails": { "CREATORID": "3813000000002003", "EmpID": "1007", "EmpName": "Randy Marsh", "Department":"Sales", "Address": "42, Tenth Street, Jacksonville, FL", "DOJ": "14 MAR 2017", "MODIFIEDTIME": "2021-08-04 09:10:14:752", "CREATEDTIME": "2021-08-04 09:10:14:752", "ROWID": "3813000000214001" } } ] }{{% /panel_with_adjustment %}} {{% panel_with_adjustment header="Sample Request: Data Updating" class="language-bash" %}}curl -X POST \ https://api.catalyst.zoho.com/baas/v1/project/4000000006007/query \ -H "Authorization: Zoho-oauthtoken 1000.910*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*16.2f*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*57 \ -H "Content-Type: application/json" \ -d '{ "query":"UPDATE EmpDetails SET Department='Operations' WHERE EmpID=1007" }' {{% /panel_with_adjustment %}} {{% panel_with_adjustment header="Sample Response: Data Updating" class="language-json line-numbers"%}} { "status": "success", "data": [ { "EmpDetails": { "CREATORID": "3813000000002003", "EmpID": "1007", "EmpName": "Randy Marsh", "Department":"Operations", "Address": "42, Tenth Street, Jacksonville, FL", "DOJ": "14 MAR 2017", "MODIFIEDTIME": "2021-08-04 09:11:54:318", "CREATEDTIME": "2021-08-04 09:10:14:752", "ROWID": "3813000000214001" } } ] }{{% /panel_with_adjustment %}} {{% panel_with_adjustment header="Sample Request: Data Deletion" class="language-bash" %}}curl -X POST \ https://api.catalyst.zoho.com/baas/v1/project/4000000006007/query \ -H "Authorization: Zoho-oauthtoken 1000.910*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*16.2f*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*57 \ -H "Content-Type: application/json" \ -d '{ "query":"DELETE FROM EmpDetails WHERE EmpID=1007" }' {{% /panel_with_adjustment %}} {{% panel_with_adjustment header="Sample Response: Data Deletion" class="language-json line-numbers"%}} { "status": "success", "data": [ { "EmpDetails": { "DELETED_ROWS_COUNT": 1 } } ] }{{% /panel_with_adjustment %}} {{% expand-container %}} {{% expand id="sdk_docs" header="SDK Documentation"%}} {{%link href="/en/sdk/java/v1/cloud-scale/zcql/execute-zcql-query"%}}Execute ZCQL Query- Java SDK{{%/link%}}</br></br> {{%link href="/en/sdk/nodejs/v2/cloud-scale/zcql/execute-zcql-query"%}}Execute ZCQL Query- Node.js SDK{{%/link%}}</br></br> {{%link href="/en/sdk/python/v1/cloud-scale/zcql/execute-zcql-query"%}}Execute ZCQL Query- Python SDK{{%/link%}}</br></br> {{%link href="/en/sdk/web/v4/cloud-scale/zcql/execute-zcql-query"%}}Execute ZCQL Query- Web SDK{{%/link%}} {{%/expand%}} {{%/expand-container%}} {{%/divtag%}} {{%/section%}} --- ## SDK — Android — ZCQL -------------------------------------------------------------------------------- title: "Execute ZCQL Query" description: "ZCQL is Catalyst's own query language that enables you to perform data creation, retrieval, and modification operations in the Data Store." last_updated: "2026-03-18T07:41:08.551Z" source: "https://docs.catalyst.zoho.com/en/sdk/android/v2/cloud-scale/zcql/execute-zcql-query/" service: "Cloud Scale" related: - Data Store (/en/cloud-scale/help/data-store/introduction) - ZCQL (/en/cloud-scale/help/zcql/introduction/) - Execute ZCQL - API (/en/api/code-reference/cloud-scale/zcql/execute-zcql-query/#ExecuteZCQLQuery) -------------------------------------------------------------------------------- # ZCQL ZCQL is Catalyst's own query language that enables you to perform data creation, retrieval, and modification operations in the {{%link href="/en/cloud-scale/help/data-store/introduction"%}}Data Store{{%/link%}}. It supports queries with built-in functions, SQL Join clauses, and other statements and conditions. ### Execute a ZCQL Query Before you execute a ZCQL query to fetch the required data set, you must construct the query to pass it to the {{%badge%}}**execute()**{{%/badge%}} method. You can learn about the ZCQL syntax from the {{%link href="/en/cloud-scale/help/zcql/introduction"%}}ZCQL help page{{%/link%}}. You must pass an instance of {{%badge%}}**ZCatalystSelectQuery**{{%/badge%}} to the {{%badge%}}execute(){{%/badge%}} method, as shown in the code syntax of a ZCQL query execution below. The {{%badge%}}&lt;DATA_STORE_INSTANCE&gt;{{%/badge%}} used here is the instance defined in the {{%link href="/en/sdk/android/v2/cloud-scale/data-store/create-data-store-instance"%}}Data Store Instance page{{%/link%}}. {{%code class="language-bash line-numbers"%}}ZCatalystApp.getInstance().getDataStoreInstance().execute( selectQuery: ZCatalystSelectQuery, success: (List&lt;Map&lt;String, Map&lt;String, Any?&gt;&gt;&gt;) → Unit, failure: ((ZCatalystException) → Unit)? ): ZCatalystRequest&lt;ZCatalystResponse&lt;List&lt;Map&lt;String, Any?&gt;&gt;&gt;&gt;&gt;? {{%/code%}} **Parameters**: * {{%badge%}}**selectQuery**{{%/badge%}}: The instance of the type {{%badge%}}ZCatalystSelectQuery{{%/badge%}} to be passed You can create a {{%badge%}}selectQuery{{%/badge%}} instance for {{%badge%}}ZCatalystSelectQuery{{%/badge%}} for the statements supported by ZCQL, in the following way: {{%code class="language-bash line-numbers"%}}ZCatalystSelectQuery.Builder() .select(columns: Set&lt;Column&gt;): ZCatalystSelectQuery.Builder .selectAll(): ZCatalystSelectQuery.Builder .where(column: String, comparator: ZCatalystUtil.Comparator, value: String): ZCatalystSelectQuery.Builder .from(tableName: String): ZCatalystSelectQuery.Builder .and(column: String, comparator: ZCatalystUtil.Comparator, value: String): ZCatalystSelectQuery.Builder .groupBy(columns: Set&lt;Column&gt;): ZCatalystSelectQuery.Builder .orderBy(columns: Set&lt;Column&gt;, sortOrder: ZCatalystUtil.SortOrder): ZCatalystSelectQuery.Builder .innerJoin(tableName: String): ZCatalystSelectQuery.Builder .leftJoin(tableName: String): ZCatalystSelectQuery.Builder .on(joinColumn1: String, comparator: ZCatalystUtil.Comparator, joinColumn2: String): ZCatalystSelectQuery.Builder .or(column: String, comparator: ZCatalystUtil.Comparator, value: String): ZCatalystSelectQuery.Builder .limit(offset: Int, value: Int?): ZCatalystSelectQuery.Builder .build(): ZCatalystSelectQuery {{%/code%}} A sample code snippet of a ZCQL query execution is shown below: {{%code class="language-bash line-numbers"%}}val query = ZCatalystSelectQuery.Builder() .selectAll() .from("EmployeeDetails") //Replace this with your table name .where("Location", ZCatalystUtil.Comparator.EQUAL_TO, "Austin") .and("Department", ZCatalystUtil.Comparator.EQUAL_TO, "Marketing") .or("isActive", ZCatalystUtil.Comparator.EQUAL_TO, "true") .limit(5) .build() ZCatalystApp.getInstance().getDataStoreInstance().execute(query, { println("Query executed successfully. $it") }, { exception -> println("Exception occured $exception") }) {{%/code%}} --- ## SDK — Flutter — ZCQL -------------------------------------------------------------------------------- title: "Execute ZCQL Query" description: "ZCQL is Catalyst's own query language that enables you to perform data creation, retrieval, and modification operations in the Data Store." last_updated: "2026-03-18T07:41:08.553Z" source: "https://docs.catalyst.zoho.com/en/sdk/flutter/v2/cloud-scale/zcql/execute-zcql-query/" service: "Cloud Scale" related: - Data Store (/en/cloud-scale/help/data-store/introduction) - ZCQL (/en/cloud-scale/help/zcql/introduction/) - Execute ZCQL - API (/en/api/code-reference/cloud-scale/zcql/execute-zcql-query/#ExecuteZCQLQuery) -------------------------------------------------------------------------------- # ZCQL ZCQL is Catalyst's own query language that enables you to perform data creation, retrieval, and modification operations in the {{%link href="/en/cloud-scale/help/data-store/introduction"%}}Data Store{{%/link%}}. It supports queries with built-in functions, SQL Join clauses, and other statements and conditions. ### Execute a ZCQL Query Flutter enables you to perform data retrieval operations using ZCQL. Before you execute a ZCQL query to fetch the required data set, you must construct the query to pass it to the {{%badge%}}**getData()**{{%/badge%}} method. You can learn about the ZCQL syntax from the {{%link href="/en/cloud-scale/help/zcql/introduction"%}}ZCQL help page{{%/link%}}. You must pass an instance of {{%badge%}}**ZCatalystSelectQuery**{{%/badge%}} to the {{%badge%}}getData(){{%/badge%}} method, as shown in the code syntax of a ZCQL query execution below. The {{%badge%}}&lt;DATA_STORE_INSTANCE&gt;{{%/badge%}} used here is the instance defined in the {{%link href="/en/sdk/flutter/v2/cloud-scale/data-store/create-data-store-instance"%}}Data Store Instance page{{%/link%}}. {{%code class="language-bash line-numbers"%}}Future<(APIResponse, List&lt;dynamic&gt;?)> ZCatalystApp.getInstance().getDataStoreInstance().getData( ZCatalystSelectQuery selectQuery){{%/code%}} **Parameters**: * {{%badge%}}**selectQuery**{{%/badge%}}: The instance of the type {{%badge%}}ZCatalystSelectQuery{{%/badge%}} to be passed You can create a {{%badge%}}selectQuery{{%/badge%}} instance for {{%badge%}}ZCatalystSelectQuery{{%/badge%}} for the statements supported by ZCQL, in the following way: {{%code class="language-bash line-numbers"%}}ZCatalystSelectQuery.Builder() .select(columns: Set&lt;Column&gt;): ZCatalystSelectQuery.Builder .selectAll(): ZCatalystSelectQuery.Builder .where(column: String, comparator: ZCatalystUtil.Comparator, value: String): ZCatalystSelectQuery.Builder .from(tableName: String): ZCatalystSelectQuery.Builder .and(column: String, comparator: ZCatalystUtil.Comparator, value: String): ZCatalystSelectQuery.Builder .groupBy(columns: Set&lt;Column&gt;): ZCatalystSelectQuery.Builder .orderBy(columns: Set&lt;Column&gt;, sortOrder: ZCatalystUtil.SortOrder): ZCatalystSelectQuery.Builder .innerJoin(tableName: String): ZCatalystSelectQuery.Builder .leftJoin(tableName: String): ZCatalystSelectQuery.Builder .on(joinColumn1: String, comparator: ZCatalystUtil.Comparator, joinColumn2: String): ZCatalystSelectQuery.Builder .or(column: String, comparator: ZCatalystUtil.Comparator, value:String): ZCatalystSelectQuery.Builder .limit(offset: Int, value: Int?): ZCatalystSelectQuery.Builder .build(): ZCatalystSelectQuery{{%/code%}} A sample code snippet of a ZCQL query execution is shown below: {{%code class="language-bash line-numbers"%}}try { ZCQLColumn column1 = ZCQLColumn('Title'); ZCQLColumn column2 = ZCQLColumn('Category'); Set&lt;ZCQLColumn&gt; columns = Set(); columns.add(column1); columns.add(column2); ZCatalystSelectQuery query = ZCatalystQueryBuilder() .select(columns) .from('Projects') .where('Category', Comparator.EQUAL_TO, 'Official') .build(); var (_, result) = await app.getDataStoreInstance().getData(query); print("Query Result: $result"); } on ZCatalystException catch (ex) { print(ex.toString()); }{{%/code%}} --- ## SDK — iOS — ZCQL -------------------------------------------------------------------------------- title: "Execute ZCQL Query" description: "ZCQL is Catalyst's own query language that enables you to perform data creation, retrieval, and modification operations in the Data Store." last_updated: "2026-03-18T07:41:08.555Z" source: "https://docs.catalyst.zoho.com/en/sdk/ios/v2/cloud-scale/zcql/execute-zcql-query/" service: "Cloud Scale" related: - Data Store (/en/cloud-scale/help/data-store/introduction) - ZCQL (/en/cloud-scale/help/zcql/introduction/) - Execute ZCQL - API (/en/api/code-reference/cloud-scale/zcql/execute-zcql-query/#ExecuteZCQLQuery) -------------------------------------------------------------------------------- # ZCQL ZCQL is Catalyst's own query language that enables you to perform data retrieval operations in the {{%link href="/en/cloud-scale/help/data-store/introduction"%}}Data Store{{%/link%}}. It supports SELECT queries with built-in functions, SQL Join clauses, and other statements and conditions. ### Execute a ZCQL Query Before you execute a ZCQL query to fetch the required data set, you must construct the query to pass it to the {{%badge%}}**execute()**{{%/badge%}} method. You can learn about the ZCQL syntax from the {{%link href="/en/cloud-scale/help/zcql/introduction"%}}ZCQL help page{{%/link%}}. You must pass an instance of {{%badge%}}**ZCatalystSelectQuery**{{%/badge%}} to the {{%badge%}}execute(){{%/badge%}} method, as shown in the code syntax of a ZCQL query execution below. {{%code class="language-bash"%}}&lt;ZCatalystApp&gt;.execute( query : ZCatalystSelectQuery, completion: @escaping (Result&lt;[ [ String : Any ] ], ZCatalystError&gt;) -> Void){{%/code%}} **Parameters**: * {{%badge%}}**query**{{%/badge%}}: The instance of the type {{%badge%}}ZCatalystSelectQuery{{%/badge%}} to be passed * {{%badge%}}**completion**{{%/badge%}}: If the query execution call is successful, the completion block will return with the records that match the criteria of the query. Else, it will return an error. You can create a {{%badge%}}query{{%/badge%}} instance for the ZCatalystSelectQuery for the statements supported by ZCQL, in the following way: {{%code class="language-bash line-numbers"%}}ZCatalystSelectQuery.Builder() .select(columns: Set&lt;Column&gt;) -> ZCatalystSelectQuery.Builder .selectAll() -> ZCatalystSelectQuery.Builder .where(column: String, comparator: Comparator, value: String) -> ZCatalystSelectQuery.Builder .from(tableName: String) -> ZCatalystSelectQuery.Builder .and(column: String, comparator: Comparator, value: String) -> ZCatalystSelectQuery.Builder .groupBy(columns: Set&lt;Column&gt;) -> ZCatalystSelectQuery.Builder .orderBy(columns: Set&lt;Column&gt;, sortOrder: SortOrder) -> ZCatalystSelectQuery.Builder .innerJoin(tableName: String) -> ZCatalystSelectQuery.Builder .leftJoin(tableName: String) -> ZCatalystSelectQuery.Builder .on(joinColumn1: String, comparator: Comparator, joinColumn2: String) -> ZCatalystSelectQuery.Builder .or(column: String, comparator: Comparator, value: String) -> ZCatalystSelectQuery.Builder .limit(offset: Int, value: Int? = nil) -> ZCatalystSelectQuery.Builder .build() -> ZCatalystSelectQuery {{%/code%}} A sample code snippet of a ZCQL query execution is shown below: {{%code class="language-bash line-numbers"%}}func testExecuteZCQL(){ var builder = ZCatalystSelectQuery.Builder() var query = builder.selectAll().from( tableName : "Bio-data" ).build() //replace your table name here ZCatalystApp.shared.getDataStoreInstance(tableIdentifier: "1096000000002071").execute( query : query) { ( result ) in switch result{ case .success( let response ) : print("Response : \( response )") case .error( let error ) : print( "Error occurred &gt;&gt;&gt; \( error )" ) } } } {{%/code%}} --- ## SDK — Java — ZCQL -------------------------------------------------------------------------------- title: "Execute ZCQL queries" description: "This page describes the method to execute ZCQL queries on a table in the Data Store in your Java application with sample code snippets." last_updated: "2026-03-18T07:41:08.557Z" source: "https://docs.catalyst.zoho.com/en/sdk/java/v1/cloud-scale/zcql/execute-zcql-query/" service: "Cloud Scale" related: - Execute ZCQL queries - API (/en/api/code-reference/cloud-scale/zcql/execute-zcql-query/#ExecuteZCQLQuery) - Execute ZCQL queries (/en/cloud-scale/help/zcql/introduction) - Data Store (/en/cloud-scale/help/data-store/introduction) -------------------------------------------------------------------------------- # ZCQL {{%link href="/en/cloud-scale/help/zcql/introduction" %}}ZCQL{{%/link%}} is Catalyst's own query language that enables you to perform data retrieval, insertion, updating, and deletion operations on the tables in the {{%link href="/en/cloud-scale/help/data-store/introduction" %}}Catalyst Data Store{{%/link%}}. You can execute a variety of DML queries using ZCQL to obtain or manipulate data, and use various clauses and statements such as the SQL Join clauses, Groupby and OrderBy statements, and built-in SQL functions. ### Execute ZCQL Queries Catalyst also provides an {{%link href="/en/cloud-scale/help/data-store/olap-database/introduction/" %}}**OLAP database**{{%/link%}}, in addition to the primary Data Store that is suited for analytical data retrieval queries. You can choose to execute simple transactional queries on the primary Data Store, and complex analytical queries that involve {{%link href="/en/cloud-scale/help/zcql/zcql-functions/" %}}ZCQL functions{{%/link%}} on the OLAP database. The queries that you execute on the primary Data Store can include SELECT, INSERT, UPDATE, or DELETE statements. The queries that you execute on the OLAP database must only include the SELECT statement, as direct write operations on it are not allowed. You must construct a ZCQL query and pass it to the {{%badge%}}executeQuery(){{%/badge%}} method for execution as shown in the sample code below. The {{%badge%}}executeQuery(){{%/badge%}} method supports these three parameters: * The String variable containing the constructed query statement * {{%badge%}}isV2?{{%/badge%}}: A boolean value ({{%badge%}}true{{%/badge%}} or {{%badge%}}false{{%/badge%}}) indicating if it is a ZCQL v2 query * {{%badge%}}isOLAP?{{%/badge%}}: A boolean value ({{%badge%}}true{{%/badge%}} or {{%badge%}}false{{%/badge%}}) indicating if the query needs to be executed on the OLAP database {{%code class="language-sql"%}}executeQuery(query: string, isV2?: boolean , isOLAP?:boolean){{%/code%}} A sample SELECT query is shown below. The response will contain the records you fetch using the SELECT query, or the response generated for the other operations. #### Sample Code Snippet <br> {{% panel_without_adjustment header="Package Imports" class="language-java line-numbers" %}}import com.zc.component.object.ZCRowObject; import com.zc.component.zcql.ZCQL;{{% /panel_without_adjustment %}} {{%code class="language-java" scroll="set-scroll" %}}//Construct the query to be executed String query = "SELECT * from empDetails limit 10"; //Get the ZCQL instance and execute query using the query string ArrayList <ZCRowObject> rowList = ZCQL.getInstance().executeQuery(query, true , false) {{%/code%}} --- ## SDK — Node JS — ZCQL -------------------------------------------------------------------------------- title: "Get ZCQL Instance" description: "This page describes the method to execute ZCQL queries on a table in the Data Store in your NodeJS application with sample code snippets." last_updated: "2026-03-18T07:41:08.559Z" source: "https://docs.catalyst.zoho.com/en/sdk/nodejs/v2/cloud-scale/zcql/get-component-instance/" service: "Cloud Scale" related: - ZCQL (/en/cloud-scale/help/zcql/introduction) - Data Store (/en/cloud-scale/help/data-store/introduction) -------------------------------------------------------------------------------- # ZCQL {{%link href="/en/cloud-scale/help/zcql/introduction" %}}ZCQL{{%/link%}} is Catalyst's own query language that enables you to perform data retrieval, insertion, updating, and deletion operations on the tables in the {{%link href="/en/cloud-scale/help/data-store/introduction" %}}Catalyst Data Store{{%/link%}}. You can execute a variety of DML queries using ZCQL to obtain or manipulate data, and use various clauses and statements such as the SQL Join clauses, Groupby and OrderBy statements, and built-in SQL functions. Catalyst also provides an {{%link href="/en/cloud-scale/help/data-store/olap-database/introduction/" %}}**OLAP database**{{%/link%}}, in addition to the primary Data Store that is suited for analytical data retrieval queries. You can choose to execute simple transactional queries on the primary Data Store, and complex analytical queries that involve {{%link href="/en/cloud-scale/help/zcql/zcql-functions/" %}}ZCQL functions{{%/link%}} on the OLAP database. ### Get Component Instance You must first create a component instance for ZCQL. The {{%badge%}}zcql{{%/badge%}} instance can be created as shown below. {{%code class="language-javascript" scroll="set-scroll" %}}//Get a ZCQL instance let zcql = app.zcql();{{%/code%}} -------------------------------------------------------------------------------- title: "Execute Query" description: "This page describes the method to execute ZCQL queries on a table in the Data Store in your NodeJS application with sample code snippets." last_updated: "2026-03-18T07:41:08.559Z" source: "https://docs.catalyst.zoho.com/en/sdk/nodejs/v2/cloud-scale/zcql/execute-zcql-query/" service: "Cloud Scale" related: - Execute query - API (/en/api/code-reference/cloud-scale/zcql/execute-zcql-query/#ExecuteZCQLQuery) - ZCQL (/en/cloud-scale/help/zcql/introduction) - Data Store (/en/cloud-scale/help/data-store/introduction) -------------------------------------------------------------------------------- # Execute Query {{%badge%}}zcql{{%/badge%}} refers to the component instance defined {{%link href="/en/sdk/nodejs/v2/cloud-scale/zcql/get-component-instance" %}}here{{%/link%}}. This will return a promise which will be resolved to an object. The {{%badge%}}content{{%/badge%}} key will contain the array of row objects. ### Construct and Execute the Query on the Primary Data Store For the {{%link href="/en/cloud-scale/help/zcql/introduction" %}}ZCQL queries{{%/link%}} to be executed on the primary Data Store, you can construct the query and pass it to the {{%badge%}}executeZCQLQuery(){{%/badge%}} method as shown below. These queries can include SELECT, INSERT, UPDATE, or DELETE statements. A sample INSERT query is shown below: {{%code class="language-javascript" scroll="set-scroll" %}}//Construct the query to execute let query = 'INSERT into ShipmentData (productID, productName, region) VALUES (3782, A4 Reams, India)'; let result = await zcql.executeZCQLQuery(query);{{% /code%}} <br> ### Construct and Execute the Query on the OLAP Database The queries that you execute on the OLAP database must only include the SELECT statement, as direct write operations on it are not allowed. You can construct the query object and pass it to the {{%badge%}}executeOLAPQuery(){{%/badge%}} method. A sample analytical SELECT query is shown below. {{%code class="language-javascript" scroll="set-scroll" %}}//Construct the query to execute let query = 'SELECT SUM(price) FROM ShipmentData'; let result = await zcql.executeOLAPQuery(query);{{% /code%}} --- ## SDK — Python — ZCQL -------------------------------------------------------------------------------- title: "Get ZCQL Instance" description: "This page describes the method to execute ZCQL queries on a table in the Data Store in your Python application with sample code snippets." last_updated: "2026-03-18T07:41:08.561Z" source: "https://docs.catalyst.zoho.com/en/sdk/python/v1/cloud-scale/zcql/get-component-instance/" service: "Cloud Scale" related: - ZCQL Help (/en/cloud-scale/help/zcql/introduction) - Data Store Help (/en/cloud-scale/help/data-store/introduction) -------------------------------------------------------------------------------- # ZCQL {{%link href="/en/cloud-scale/help/zcql/introduction" %}}Catalyst Cloud Scale ZCQL{{%/link%}} is Catalyst's own query language that enables you to perform data retrieval, insertion, updation, and deletion operations on the tables in the Catalyst Cloud Scale Data Store. You can execute a variety of DML queries using ZCQL to obtain or manipulate data, and use various clauses and statements such as the SQL Join clauses, Groupby and OrderBy statements, and built-in SQL functions. Catalyst also provides an {{%link href="/en/cloud-scale/help/data-store/olap-database/introduction/" %}}**OLAP database**{{%/link%}}, in addition to the primary Data Store that is suited for analytical data retrieval queries. You can choose to execute simple transactional queries on the primary Data Store, and complex analytical queries that involve {{%link href="/en/cloud-scale/help/zcql/zcql-functions/" %}}ZCQL functions{{%/link%}} on the OLAP database. ### Get a Component Instance A component instance is an object that can be used to access the predefined configurations specific to a particular component. This process will not fire a server-side call. The {{%badge%}}app{{%/badge%}} reference used in the code below is the Python object returned as a response during SDK initialization. Also note that this instance will be used in multiple scenarios while performing retrieval, insertion, updating, or deleting operations in the Catalyst Data Store. {{%code class="language-python" scroll="set-scroll" %}}#Get a ZCQL component instance zcql_service = app.zcql() {{%/code%}} -------------------------------------------------------------------------------- title: "Execute Query" description: "This page describes the method to execute ZCQL queries on a table in the Data Store in your Python application with sample code snippets." last_updated: "2026-03-18T07:41:08.561Z" source: "https://docs.catalyst.zoho.com/en/sdk/python/v1/cloud-scale/zcql/execute-zcql-query/" service: "Cloud Scale" related: - Execute query - API (/en/api/code-reference/cloud-scale/zcql/execute-zcql-query/#ExecuteZCQLQuery) - ZCQL Help (/en/cloud-scale/help/zcql/introduction) - Data Store Help (/en/cloud-scale/help/data-store/introduction) - SDK Scopes (/en/sdk/python/v1/sdk-scopes) -------------------------------------------------------------------------------- # Execute Query The {{%badge%}}zcql_service{{%/badge%}} reference used in the code snippet below is the component instance created {{%link href="/en/sdk/python/v1/cloud-scale/zcql/get-component-instance/" %}}earlier{{%/link%}}. Based on the query object being passed, the response returns a row object or an array of row objects. ### Construct and Execute the Query on the Primary Data Store For the ZCQL queries to be executed on the primary Data Store, you can construct the query object and pass it to the {{%badge%}}execute_query(){{%/badge%}} method as shown below. These queries can include SELECT, INSERT, UPDATE, or DELETE statements. A sample INSERT query is shown below: {{%code class="language-javascript" scroll="set-scroll" %}}#Construct the ZCQL query query = 'INSERT into ShipmentData (productID, productName, region) VALUES (3782, A4 Reams, India)' result = zcql_service.execute_query(query){{% /code%}} ### Construct and Execute the Query on the OLAP Database The queries that you execute on the OLAP database must only include the SELECT statement, as direct write operations on it are not allowed. You can construct the query object and pass it to the {{%badge%}}execute_olap_query(){{%/badge%}} method. A sample analytical SELECT query is shown below. {{%code class="language-javascript" scroll="set-scroll" %}}//Construct the query to execute query = 'SELECT SUM(price) FROM ShipmentData'; result = zcql_service.execute_olap_query(query);{{% /code%}} **Parameters Used** <table class="content-table"> <thead> <tr> <th class="w20p">Parameter Name</th> <th class="w20p">Data Type</th> <th class="w60p">Definition</th> </tr> </thead> <tbody> <tr> <td>{{%badge%}}query{{%/badge%}}</td> <td>String</td> <td>A {{%bold%}}Mandatory{{%/bold%}} parameter. Will store the query to be executed.</td> </tr> </tbody> </table> {{%note%}}{{%bold%}}Info :{{%/bold%}} Refer to the {{%link href="/en/sdk/python/v1/sdk-scopes" %}}SDK Scopes table{{%/link%}} to determine the required permission level for performing the above operation.{{%/note%}} --- ## SDK — Web — ZCQL -------------------------------------------------------------------------------- title: "Get a Component Instance" description: "ZCQL is Catalyst's own query language that enables you to perform data retrieval operations in the Data Store." last_updated: "2026-03-18T07:41:08.562Z" source: "https://docs.catalyst.zoho.com/en/sdk/web/v4/cloud-scale/zcql/get-component-instance/" service: "Cloud Scale" related: - Data Store (/en/cloud-scale/help/data-store/introduction) - ZCQL (/en/cloud-scale/help/zcql/introduction/) - Execute ZCQL - API (/en/api/code-reference/cloud-scale/zcql/execute-zcql-query/#ExecuteZCQLQuery) -------------------------------------------------------------------------------- # ZCQL {{%link href="/en/cloud-scale/help/zcql/introduction/"%}}ZCQL{{%/link%}} is Catalyst's own query language that enables you to perform data retrieval, insertion, updating, and deletion operations on the tables in the {{%link href="/en/cloud-scale/help/data-store/introduction"%}} Catalyst Data Store{{%/link%}}. You can execute a variety of DML queries using ZCQL to obtain or manipulate data, and use various clauses and statements such as the SQL Join clauses, Groupby and OrderBy statements, and built-in SQL functions. ### Get a Component Instance The {{%badge%}}zcql{{%/badge%}} reference can be created as shown below. This does not a fire server-side call. {{%code class="language-javascript"%}}//Get a ZCQL instance var zcql = catalyst.ZCatalystQL; {{%/code%}} -------------------------------------------------------------------------------- title: "Execute ZCQL Query" description: "ZCQL is Catalyst's own query language that enables you to perform data retrieval operations in the Data Store." last_updated: "2026-03-18T07:41:08.562Z" source: "https://docs.catalyst.zoho.com/en/sdk/web/v4/cloud-scale/zcql/execute-zcql-query/" service: "Cloud Scale" related: - Data Store (/en/cloud-scale/help/data-store/introduction) - ZCQL (/en/cloud-scale/help/zcql/introduction/) - Execute ZCQL - API (/en/api/code-reference/cloud-scale/zcql/execute-zcql-query/#ExecuteZCQLQuery) -------------------------------------------------------------------------------- # Execute ZCQL Query ### Construct the Query You must construct a {{%link href="/en/cloud-scale/help/zcql/introduction/"%}}ZCQL query{{%/link%}} on the required data set before you execute it. A sample SELECT query is shown below: {{%code class="language-javascript"%}}//Create a query to execute var query = 'SELECT * FROM ShipmentData'; {{%/code%}} ### Execute the Query The query object created in the step above is passed to the executeZCQLQuery() method. The zcql reference used here is the {{%link href="/en/sdk/web/v4/cloud-scale/zcql/get-component-instance"%}}component instance{{%/link%}} defined earlier. This will return a promise which will be resolved to an object. The content key will contain the array of row objects. {{%code class="language-javascript"%}}//Execute the query by passing it var zcql = catalyst.ZCatalystQL; var zcqlPromise = zcql.executeQuery(query); zcqlPromise .then((response) => { console.log(response.content); }) .catch((err) => { console.log(err); }); {{%/code%}} {{%note%}}{{%bold%}}Note:{{%/bold%}} To use {{%link href="/en/cloud-scale/help/zcql/syntax-exceptions/" %}}ZCQL V2{{%/link%}} commands in your code, use the Catalyst methods listed {{%link href="/en/sdk/web/v4/overview/#manage-environment-variables" %}}here{{%/link%}} with the values listed below to set the appropriate environment variable:<br /> * {{%bold%}}Key{{%/bold%}}: {{%badge%}}ZOHO_CATALYST_ZCQL_PARSER{{%/badge%}} * {{%bold%}}Value{{%/bold%}}: {{%badge%}}V2{{%/badge%}}{{%/note%}} A sample response that you will receive is shown below. The response is the same for both versions of Web SDK. {{% panel_with_adjustment class="language-json line-numbers" header="Web SDK" %}}[ { AlienCity: { CREATORID: "2136000000006003", MODIFIEDTIME: "2021-08-13 13:49:19:475", CREATEDTIME: "2021-08-13 13:49:19:475", CityName: "Dallas", ROWID: "2136000000008508" } }, { AlienCity: { CREATORID: "2136000000006003", MODIFIEDTIME: "2021-08-16 15:55:32:969", CREATEDTIME: "2021-08-16 15:55:32:969", CityName: "Houston", ROWID: "2136000000011002" } }, { AlienCity: { CREATORID: "2136000000006003", MODIFIEDTIME: "2021-08-16 17:03:01:507", CREATEDTIME: "2021-08-16 16:29:10:499", CityName: "Austin", ROWID: "2136000000011011" } } ] {{% /panel_with_adjustment %}}