---
title: "WHERE Clause"
url: https://docs.catalyst.zoho.com/en/cloud-scale/help/zcql/where/
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-08-28T06:26:16Z
service: Cloud Scale
type: general
---

# WHERE Clause

### Introduction

The `WHERE` 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 `WHERE` condition in a `SELECT` statement is:

```sql
SELECT column_name(s) FROM base_table_name WHERE condition
```

The `WHERE` condition can be used with the `UPDATE` and `DELETE` statements as well.

### Operators Supported by the WHERE Clause

You can use the following operators in the `WHERE` 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>

**Example**:

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 `WHERE` clause in the following way:

```sql
SELECT MovieName, ShowDate FROM Movies WHERE ShowDate IS '2018-07-14'
```

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>

### Multiple WHERE Conditions

You can specify a maximum of five WHERE conditions in a single query. When multiple `WHERE` conditions are specified, you can use either an `AND` or an `OR` operator to link the conditions together. The functionalities of the `AND` and `OR` operators are:

* `AND`: Produces only the data records that satisfy both of the conditions that are associated with the `AND` operator.

* `OR`: Produces the data records that satisfy either of the conditions that are associated with the `OR` operator.

For example, the syntax for using multiple `WHERE` conditions in a `SELECT` statement is:

```sql
SELECT column_name(s)
FROM base_table_name
WHERE condition_1 AND|OR condition_2.. AND|OR condition_5
```

**Example**:

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:

```sql
SELECT MovieName, ShowDate, ShowTime FROM Movies WHERE MovieName='The First Purge' OR MovieName='Skyscraper' AND ShowDate='2018-07-14'
```

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>

### LIKE Statement

The `LIKE` 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 `LIKE` condition in an `UPDATE` statement is as follows:

```sql
UPDATE base_table_name
SET column_name(s)=value(s)
WHERE condition
LIKE *value* 
```

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 `LIKE` value as 'A\*'. This specifies that after the letter A, any values can follow.

**Example:**

To update the records where the movie names begin with the letter 'S' in the _Movies_ table, execute the following query:

```sql
UPDATE Movies SET ShowDate='2018-07-17' WHERE MovieName like 'S*'
```

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>

Wildcard syntax and symbols such as **\*** and **?** are also permitted to be used in ZCQL statements defined using the `LIKE` condition statement.

<table class="content-table">
	<thead>
		<tr>
			<th class="w30p">Wildcard Syntax</th>
			<th class="w70p">Definition</th>
		</tr>
	</thead>
	<tbody>
		<tr>
			<td>`\*`</td>
			<td>Matches zero or more characters</td>
		</tr>
        <tr>
			<td>`?`</td>
			<td>Matches exactly one character</td>
		</tr>
	</tbody>
</table>

### BETWEEN Statement

The `BETWEEN` 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 `BETWEEN` condition in a `DELETE` statement is as follows:

```sql
DELETE FROM base_table_name
WHERE condition
BETWEEN Value1 AND Value2
```

> **Note:** You can use the `BETWEEN` 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.

**Example:**

To delete the records where the value of the MovieID is between 2056 and 2059 in the _Movies_ table, execute the following query:

```sql
DELETE FROM Movies WHERE MovieID BETWEEN 2056 AND 2059
```

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:** To produce the exact opposite result, you can use the `NOT BETWEEN` statement. The same syntax and conditions of a `BETWEEN` statement applies to `NOT BETWEEN` statements. 

### 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 `WHERE` clause is applicable to the `SELECT`, `UPDATE` and `DELETE` queries.

You can compare any two columns that are of the following data types : `BOOLEAN`, `DOUBLE`, `DATE`, `DATETIME`, `ENCRYPTED`, `VARCHAR`, `TEXT`, `INT` and `BIGINT`.

> **Note:** You cannot compare columns of two different data types.

**SELECT:**

The general syntax for column comparison within the same table is as follows:

```sql
SELECT * FROM tablename WHERE columnname1 = tablename.columnname2
```

**Example**:

```sql
Select MovieName FROM Movies WHERE Actor = Movies.Producer
```

<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:**

```sql
UPDATE Movies SET self_produced = "Yes" WHERE Actor = Movies.Producer
```

**DELETE:**

```sql
DELETE FROM Movies WHERE Actor = Movies.Producer
```

#### Comparison of Columns in Different Tables

You can also compare columns of two different tables as shown in the syntax below :

```sql
SELECT tablename1.columnname1, tablename2.columnname1 
FROM tablename2 LEFT JOIN tablename1 ON tablename2.columnname1 = tablename1.columnname2 
WHERE tablename1.columnname1 = tablename2.columnname2
```

**Example**:

```sql
SELECT Movies.name, Theatres.city from Theatres left join Movies on Theatres.ROWID = Movies.theatreID where Movies.distributionCity = theatre.city
```

<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>

Similarly, you can compare columns belonging to two different tables while executing the `UPDATE` and `DELETE` 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 `WHERE` clause.  You can write subqueries with the `WHERE` clause in `SELECT`, `UPDATE` and `DELETE` queries.

The syntax of a subquery with `WHERE` clause is given below:

```sql
SELECT column_name FROM table_name WHERE column_name operator (SELECT column_name FROM table_name)
```

**Example Database**

The employee details of Zylker Technologies are being maintained in the [Data Store](https://docs.catalyst.zoho.com/en/cloud-scale/help/data-store/introduction/) 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:

```sql
SELECT Name, Salary FROM Zylker_Employee_DB WHERE Salary > (SELECT MIN (Salary) FROM Zylker_Employee_DB)
```

<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:** 

* This functionality is only available in **ZCQL V2**.

* From **December 01st, 2024**, all your current projects in all your [Orgs](https://docs.catalyst.zoho.com/en/getting-started/catalyst-organizations/) present in the [Development Environment](https://docs.catalyst.zoho.com/en/deployment-and-billing/environments/development-environment/) will be automatically mapped to **ZCQL V2 Parser**.

* From **April 01st, 2025**, all the projects present in all *Orgs* that have already been mapped to **ZCQL V2 Parser** in *Development Environment* will be automatically mapped to **ZCQL V2 Parser** in the [Production Environment](https://docs.catalyst.zoho.com/en/deployment-and-billing/environments/production-environment/), if and when production is enabled for the project.